QlikView Scripting 4: Sorgu Özellikleri ve Fonksiyonları

• Yeni QlikView oluşturduğunuzda Kod Düzenle ekranında değerlerin (numara, para değeri, tarih) formatı belirlenir. Satır başındaki çekiç işaretine tıklayarak isterseniz bu değerleri değiştirebilirsiniz.
• Her başarılı bağlantı, kendisinden önceki bağlantıyı keser. Başarılı bir başka bağlantı öncesi bağlantıyı kesmek isterseniz sorgunun kesmek istediğiniz anına denk gelen satırına DISCONNECT komutunu yazmanız yeterlidir.
• Sorgular, en üstten aşağı ve en sol sekmeden sağa doğru çalışır.
• Rapora yüklediğiniz iki farklı tabloda aynı isimle alanlar var ise bu iki tablo arasında bu iki alan üzerinden bağlantı kurulur. Bağlantı kurulmasını istemiyorsanız bu tablolardan birinde yer alan ve bağlantı kuran alanın ismini değiştirmeniz gerekmektedir.
• Eğer aynı alan ismini birden fazla tabloda kullanmakta ısrarcıysanız QUALIFY komutunu kullanabilirsiniz. 

Aşağıdaki sorgu sonucunda yandaki gibi alan isimlerine sahip bir tablo oluşacak:
QUALIFY *;
UNQUALIFY Priority, Stage;
DefectTable:
LOAD * INLINE [
Defect, Group, Priority, Stage
1, Reports, High, Queued
2, Data, Low, Reported
3, Data, High, Closed
4, Object, Medium, Rejected
5, Security, High, Reported
6, System, Medium, Rejected
7, Security, High, Closed];
Bu sayede alan isimleri başında tablo ismiyle birlikte yer alacağından bir başka tablodaki aynı isimli alanla bağlantı kurmayacak.

• Raporunuza birden fazla tablo yüklemiş ve her birinin alan ismini değiştirmekle uğraşmak istemiyor veya değiştirmek istediğiniz alanlar ile yeni alan isimlerini bir yerde toplayıp alan isim değişikliği yapmak istiyorsanız QlikView'in MAPPING özelliğini RENAME FIELD komutuyla kullanarak hızlı çözüm üretebilirsiniz.

Raporumuza yüklediğimiz tablolardaki tüm alanların isimlerini ayrı bir tabloda topladık ve karşılarına yeni isimlerini yazdık. Sonrasında bu tabloyu raporumuza yükleyip RENAME FIELD komutunu kullanarak tek seferde istediğimiz alan isimlerini verebiliriz:
Map_Table:

MAPPING LOAD 

DATA_FIELD_NAME, 

DISPLAY_FIELD_NAME

FROM Mapping_Fields.QVD (QVD);

RENAME FIELDS USING Map_Table;

• QlikView'de sorgular üzerinde kullanabileceğiniz bazı komutlar:
• if…then…elseif…else…end if
• For…next
• For each…next
• Do…loop
• Switch…case…default…end switch
• Exit script
• Sub…end sub
• Call

İyi çalışmalar..

Standart Tabloları: Bir Sütundaki Değerleri Ayrı Sütunlara Çevirmek

Elimizdeki veri ile uğraşırken zaman zaman sütun başlıklarını satıra, satırlarının her birini ayrı bir sütuna dönüştürme ihtiyacı duyuyoruz. Sütun başlıklarını satıra çevirmek için bir önceki yazımda anlattığım Dosya Sihirbazı'nı kullanabiliyoruz. Fakat sütunda yer alan değerlerin her birini ayrı bir sütuna çevirmek için orijinal adıyla Generic table veya diğer ismiyle Standard table'lar oluşturmamız gerekiyor.

Örneğin elimizde yukarıdaki resimdeki gibi bir veri olsun. Ve biz Stage sütununu satırlara çevirmek (yani tabloya beş yeni sütun -queued, reported, closed, rejected ve verified- eklemek) istiyoruz. Yani elimizdeki veriyi aşağıdaki tabloya dönüştüreceğiz.

Elimizdeki veriyi raporumuza ekleyelim. Tablonun INLINE sorgusu aşağıdaki gibidir:

DefectTable:

LOAD * INLINE [

Defect, Group, Priority, Stage

1, Reports, High, Queued

2, Data, Low, Reported

3, Data, High, Closed

4, Object, Medium, Rejected

5, Security, High, Reported

6, System, Medium, Rejected

7, Security, High, Closed

8, Object, Low, Queued

9, Data, Medium, Closed

10, Requirements, Medium, Verified];

Veriyi aktardıktan sonra satırlara çevireceğimiz sütunun her değeri için ayrı bir tablo oluşturuyoruz. Bunun için  QlikView'in GENERIC LOAD özelliğini kullanacağız.

QlikView GENERIC LOAD'un sözdizimi şu şekildedir:

GENERIC LOAD 

   [diğer tablolarla bağlantı kurduracak anahtar alan],

   [değerleri sütunlara çevrilecek sütun adı],

   [yeni sütunların değerleri]

FROM/RESIDENT [kaynak]

Bizim verimize göre GENERIC LOAD sorgumuz şu şekilde olacak:

Workflow:

GENERIC LOAD 

   Defect, 

   Stage, 

   'x' 

RESIDENT DefectTable;

Sonrasında soldaki resimde olduğu gibi her bir sütun değeri için ayrı bir tablo olarak 5 artı kaynak tablomuz olmak üzere toplamda 6 tablomuz olacak.

Her bir sütun değerini ayrı bir sütun yapmak için yola çıkmıştık. Evet, şimdi her bir değerimizin yer aldığı tabloyu ana tablomuzla birleştirerek istediğimiz formatta tek bir tablo elde edeceğiz.

Bunun için tek tek her bir bir değer tablosunu ana tablomuz için LEFT JOIN yapıyoruz:

FOR t = 0 to NoOfTables()

TableList:

LOAD 

   TableName($(t)) as Tablename

   AUTOGENERATE 1

WHERE WildMatch(TableName($(t)), 'Workflow.*');

NEXT t

FOR t = 1 to FieldValueCount('Tablename')

LET vTable = FieldValue('Tablename', $(t));

LEFT JOIN (DefectTable) 

LOAD * 

RESIDENT $(vTable);

DROP TABLE $(vTable);

NEXT t

DROP TABLE TableList;

Ve istediğimiz gibi, en başta elimizdeki verinin Stage sütununda yer alan her bir değeri ayrı bir sütuna çevirmeyi başardık!

İyi çalışmalar..

QlikView Scripting 3: Dışarıdan Yüklenen Verinin Dönüşümü

Bildiğiniz gibi QlikView raporunuza XLS, XLSX, CSV, TXT XML ve HTML formatındaki bir dosyada yer alan veriyi aktarabilirsiniz. Bu veriyi aktarırken gereksiz kısımları silmek veya verinin şekil formatını değiştirmek gibi bazen veride dönüşüm yapmaya ihtiyacınız oluyor. Dosya Sihirbazı ekranı, yüklediğiniz dosyada yapmak istediğiniz tüm bu değişiklikler için size imkan veriyor.

Peki dosyayı nasıl yükleyecek ve bu ekrana nasıl ulaşacağız? Kod Düzenle ekranının alt kısmında yer alan panelin Veri sekmesinde Tablo Dosyaları...'na tıklayarak yüklemek istediğimiz dosyayı seçeriz.

Dosyayı seçip tamam dediğimizde Dosya Sihirbazı ekranı karşımıza çıkar. Karşımıza çıkan bu ilk ekrandaki kısımlarda yaptığımız seçimlerin yüklediğimiz tabloya etkisini de Dosya Sihirbazı ekranında görebiliyoruz.

• Dosya Türü kısmında, yüklediğimiz dosyanın türünü bir daha bize teyit ettiriyor. 
• Tablolar kısmında, dosyadaki hangi tabloyu yüklemek istediğimizi seçtiriyor. 
• Üstbilgi Boyutu kısmında, seçtiğimiz tablonun üstten kaçıncı satır ile veri almaya başlamasını belirliyoruz.
• Etiketler kısmında, sütunların isimlerinin ne olmasına (dosyada yer aldığı gibi mi yoksa sırasıyla rastgele başlık mi atansın) karar veriyoruz. Eklenmiş Etiketler'i seçersek dosyanın ilk satırını sütun isimleri olarak görüyor. Hiçbiri'ni seçersek ilk satırın üstüne bir satır daha ekleyip sırasıyla rastgele başlık atıyor ve tablonun ilk satırı, diğer satırlar gibi veri değerlerinin yer aldığı satır haline geliyor.

Akabinden Sonraki diyerek devam ediyoruz. Karşımıza çıkan ekranda Dönüştürme Adımını Etkinleştir'i seçerek yüklediğimiz verinin şeklinde yapmak istediğimiz değişiklikleri yapabileceğimiz ekrana ulaşıyoruz. Bu ekranda seçimler yaparak istediğiniz veya belli bir kurala uymayan satırları silebilir, veya belli bir kural ile boş kısımları doldurabilir, yeni sütun ekleyebilir veya tabloyu sağa/sola istediğiniz tarafa çevirebilirsiniz.

Sonraki diyerek devam ettiğimizde karşımıza çıkan ekranda tüm yaptıklarımız sonrasında yüklediğimiz tablonun durumunu kontrol edip yine Sonraki dediğimizde yaptığımız tüm değişiklikleri tabloya yansıtarak tabloyu raporumuza aktarmamızı sağlayacak sorguyla karşılaşırız. Son dediğimizde sorgu, raporumuzda Kod Düzenle ekranına eklendiğini görürüz.

İyi çalışmalar..

QlikView Scripting 2: Sorgu Yazmak

Merhaba,

Sorgu (terminolojide SQL), QlikView uygulamasının kalbidir. Kaynağınızdan veriyi sorgu yardımıyla raporunuza aktarırsınız. Kaynağınızın çeşidine (Oracle, Teradata, MS SQL Server, MS Access..) uygun sorgu yazmanız gerekmektedir; yani çalışmasını istediğiniz sorgu, veri tabanınızın SQL programında da çalışabilir olması gerekiyor. QlikView'in sorgu ekranını basit bir SQL programı olarak düşünebilirsiniz.

• QlikView raporunuzun sorgusunu Kod Düzenle ekranından (Ctrl+E kısayoluyla da ulaşabilirsiniz) düzenleyebilirsiniz.
• Kod Düzenle ekranını ilk açtığınızda default genel numara, para, zaman ve tarih genel ayarlarının kodları sizi karşılar.
• Kullanıcılardan sorguyu gizlemenizi sağlayacak Gizli Kod özelliği var. Özellikle güvenlik kaygısıyla Section Access kodlarını gizlemek için kullanabilirsiniz.
• Kod Düzenle ekranında Dosya|Gizli Kod Oluştur... ile gizlemek istediğiniz sorgular için bu özelliği kullanabilirsiniz.
• Gizli Kod her zaman sekmelerin en solunda yer alır. Yani tüm diğer sekmelerden önce çalışır. Ayrıca çalışırken ekranda bu sorguya ait herhangi birşey göremezsiniz ve bu kısım loglanmaz.
• Başka bir QlikView dosyasındaki veriyi Gizli Kod ile yüklemeyemezsiniz (Binary Load).
• Kod Düzenle ekranında Dosya|Dış Kod Dosyası Aç'ı seçerek .qvs veya .txt formatında kaydedilmiş bir sorguyu raporunuzda açabilirsiniz.
• Raporda Ayarlar|Kulllanıcı Tercihleri|Kaydet ekranında Yeniden Yüklemeden Önce Kaydet'i seçerek rapor her yüklemenizden önce otomatik olarak kaydedebilirsiniz.
• Raporunuzda Dosya|Tablo Görüntüleyicisi'yi (Ctrl+T kısayoluyla da ulaşabilirsiniz) seçerek sorguyla oluşturduğunuz tabloları ve tabloların birbirleriyle ilişkilerini görebilirsiniz.
• Kod Düzenle ekranında Düzenle|Yorum veya Düzenle|Yorumu Kaldır ile sorgudaki herhangi bir satırı hızlıca yoruma çekebilir veya yorumdan kaldırabilirsiniz.
• Kod Düzenle ekranında Ekle|Ortam Değişkenleri ile yeni dosyanın sorgu kısmında default olarak gelen değerlerin formatlarını ekleyebilirsiniz.
• Kod Düzenle ekranında Ekle|Include Deyimi ile tekrar tekrar kullanacağınız bir sorguyu sorgunuza ekleyebilirsiniz. Bunun güzelliği, eklediğiniz sorgunun kaynağını değiştirdiğinizde otomatik olarak bu sorgunuzda da değişecek.
• Kod Düzenle ekranında Ekle|Yükle Deyimi ile var olan bir tablonuzu sorguya aktarabilir veya açılacak pencereyle excelde tablo oluşturur gibi sorgunuz için tablo oluşturabilirsiniz.
• Kod Düzenle ekranında Ekle|Bölüm Erişimi ile raporunuza erişim güvenliği oluşturabilirsiniz. Bu kısmın özel bir adı var: Section Access. Bu konuyu bir başka yazımda daha detaylı inceleyeceğim.
• QlikView'de farklı sekmelere yazdığınız sorgular sol baştan başlayarak çalışır.
• Kod Düzenle ekranında Sekme|Öncekiyle Birleştir... ile iki farklı sekmedeki sorgunuzu önce olan sekmede bir araya getirip sonraki sekmeyi silmiş olursunuz.
• Kod Düzenle ekranında Araçlar|Söz Dizimi Kontrolü ile sorgunuzu çalıştırmadan önce doğruluğunu kontrol edebilirsiniz.
• Sorgu ekranın alt kısmında bulunan panelin Fonksiyonlar kısmında sorgunuzda kullanabileceğiniz tüm fonksiyonları kategorize edilmiş halde bulabilir ve kolayca sorgunuza kopyalayabilirsiniz. Fonksiyonların adlarının alt kısmında kullanım örnekleri bulunmaktadır.
• Sizden sonra raporunuz üzerinde çalışanlara kolaylık olması için sorgunuza üç şekilde yorum ekleyebilirsiniz:
• REM ve ; [noktalı virgül] arasına istediğiniz formatta yorumunuzu yazabilirsiniz
• Yorumunuzu yazar, sonrasında yorumunuzun yer aldığı her satırının başına //  [iki taksim işareti] eklersiniz
• Veya yorum olarak yazdığınız kısmı seçip Düzenle|Yorum ile kolayca yorum haline getirebilirsiniz
• Sorgularınızı sekmelere ayırma kuralı size kalmış. Ben veri kaynağını ve raporun ara yüzünü esas alarak sorgumu sekmelere ayırıyorum.

QlikView Scripting 1: Veri Kaynağına Bağlantı Kurmak

• Öncesinde bazı kısaltmaları belirtelim:
• QVW: QlikView Worksheet
• OLE DB: Object Linking and Embedding Database
• ODBC: Open Database Connectivity
• Oluşturacağınız QlikView dosyasının uzantısı .qvw olacak.
• Veri kaynağınızla bağlantısı kurabilmek için öncesinde database türünüzün driver'ının uygulamayı kullanacağınız makinaya kurulmuş olması gerekmektedir.
• Denetim Masası|Yönetimsel Araçlar|ODBC Veri Kaynağı Yöneticisi'nden (OLE DB için Kullanıcı DSN, ODBC için Sistem DSN)takibini yapabilirsiniz. "ODBC" diye arayarak da hızlıca bulabilirsiniz. Bit versiyonunuza uygun Yöneticisi Araçlar'ıyla çalışmayı unutmayın.
• OLE DB; hız, bağlantı ve performans bakımından ODBC'den daha iyidir.
• ODBC ile çalışırken uygulamanın bit versiyonuna uygun olarak driver yüklemeyi unutmayın:
• 32bit QV kullanıyorsanız sadece 32bit'lik driver'larla çalışabilirsiniz.
• 64bit QV kullanıyorsanız hem 32 hem 64 bit'lik driver'larla çalışabilirsiniz. Ancak 64bit QV'yi 64 bit'lik driver ile çalıştırırsanız 32 bit'likten daha etkili performans elde edersiniz.

• Tüm bunlardan sonra QlikView uygulamasında Kod Düzenle ekranında Veri kısmından bağlantınızı kurabilirsiniz.
• Birden fazla database bağlantısı kurabilirsiniz fakat raporunuz çalışırken kurulan her başarılı bağlantı, kendisinden önceki kurulmuş bağlantıyı keser.

Merhaba

QlikView Scripting kitabı üzerinde aldığım notları konu konu burada sizlerle paylaşacağım. Umarım, notlar işinize yarar.

İyi çalışmalar..

Birden Fazla Boyutlu Grafikte Tek Boyutun İlk 5 Değerini Listeleme

QlikView'de oluşturacağınız tek boyutla oluşturduğunuz grafikte büyükten küçüğe sıralamasında ilk 5 değeri (list top 5 value based on one dimension on the chart) gösterebileceğiniz gibi birden fazla boyutla oluşturduğunuz grafikte de bir boyutun toplamı üzerinden büyükten küçüğe sıralamasında ilk 5 değeri (list top 5 value based on one dimension on the chart with multiple dimensions)  gösterebilirsiniz.

Örnek olarak aşağıdaki veriyi ele alalım. İl bazında dönemsel olarak çeşitli sebeplerden ötürü iptal edilen abone sayıları var:

İl, dönem ve sebep kırılımında ayrı ayrı iptal sayılarını bulalım ve iptal sayısına göre büyükten küçüğe sıralayalım:

Bu oluşturduğumuz üç grafikten herhangi birinde ilk 5 değeri göstermenin iki yolu var. Her iki yolu açıklama altına eklediğim videolarda "Sebep Kırılımda İptal Adetleri" isimli grafiğin benzerlerini oluşturup göstereceğim.
1. Yol: Önce Sırala (Sort) sekmesinde formulümüzü birinci sıraya taşıyıp azalana göre sıralama yapmak ve sonrasında Sunum (Presentation) sekmesinde kısıt koymak:

2. Yol: Boyut Sınırları (Dimension Limits) sekmesinde kısıt koymak. Eğer geri kalan değerleri de göstermek isterseniz değerlerinizi 5 yerine 6 ile kısıtlayıp Seçenekler (Options) kısmında diğerlerine isim vermeniz gerekiyor ki ben videoda "OTHERS" diyorum. Diğerlerini göstermeyecekseniz 5 ile kısıtlamanız gerekir. Videonun sonunda kontrol amaçlı sıralama yapıyorum:

Grafiğinizde birden fazla boyut var ise ve siz bunlardan birini esas alarak ilk 5 değeri göstermek istiyorsanız videoda gösterdiğim gibi 2.yoldan gidip sonrasında Seçenekler (Options) kısmında Global Gruplama Modu (Global Grouping Mode) kutucuğunu işaretlemeniz gerekmektedir. Sonuç olarak baktığımızda ilk 5 değere sahip değerlerin her il ve dönemdeki verisinin geldiğini göreceğiz:

Çalışma dosyasını buradan indirebilirsiniz.

İyi çalışmalar..

Kümülatif Tekil Saydırma

Bildiğiniz gibi QlikView'de raporun sorgusunda yapmak isteceğiniz bazı işlemleri set analysis yardımıyla önyüzde oluşturacağınız objeler üzerinde de yapabilirsiniz. Özellikle son kullanıcılar (end-users) için excel'de yapabildikleri ve yapamayacakları birçok işlem için set analysis yardımcı olmaktadır. Şimdi sizinle set analysis'in en güzel örneklerinden birini paylaşacağım.

Örnek olarak müşteri sayısını ele alacağız. Dönemsel olarak kümülatif tekil müşteri adedini (cumulative distinc customer id count by period) saydırmak isterseniz "island approach" dediğimiz yöntemi kullanmanız gerekmektedir. 

Elimizde aşağıdaki veri olsun. Ekran görüntüsünde yer alan "Dönemsel Tekil Müşteri Adedi" isimli grafikte dönemsel tekil müşteri adedini buluyoruz:

Geriye dönük kıyaslamayı yapabilmek için sorgu (script) kısmında [Ctrl+E kısa yoluyla kod düzenle (edit script) ekranına kolayca ulaşabilirsiniz] elimizdeki dönemleri tekil olarak tutan bir tablo daha oluşturmamız gerekiyor: 

Şimdi grafiğimizde yapmamız gereken değişiklikler:

• Boyutlar'da (Dimensions) yer alan dönem objesini [bizim örneğimizde PERIOD] sonradan oluşturduğumuz tabloda yer alan tekil dönem objesi [bizim örneğimizde PERIOD2] ile değiştirmeliyiz.
• Dönemsel kümülatif tekil müşteri adedini bulmak için İfadeler'e (Expressions) yeni bir ekleme yapacağız: COUNT(DISTINCT IF(PERIOD <= PERIOD2, CUST_ID))

Ve "Dönemsel Kümülatif Tekil Müşteri Adedi" adıyla grafiğimizi tamamladığımızda sonuç:

Çalışma dosyasını buradan indirebilirsiniz.

İyi çalışmalar..