QlikView 語法攻略 - export Excel and Email

提供大家一個簡單的滙出EXCEL 加選擇條件, 並且可以EMAIL 的Marco 語法.
下方語法是使用VBScript , 要注意要選擇System Access 跟Allow system access

另外,如果你不是使用button 呼叫Marco時, 而是Reload 後就要執行Marco 的話,
原則上無法背景處理,及QMC 上排程.
所以要另外下指令,而指令的差異是/r 變成 /l  指的是 Open, Reload *
所以注意我在MARCO 裡加了ActiveDocument.Save  
                                         ActiveDocument.GetApplication.Quit 
讓APP可以關閉

 請參考指令方法Command line execute Reload / partial Reload 指令更新資料

Sub  ExportM1023
 
 
     set f = ActiveDocument.Fields("User.BusinessUnitIdName") 'User.BusinessUnitIdName 是欄位名稱
     f.Select    "業務二處"  
     f.ToggleSelect "業務三處"   '多選擇資料時用
   
set obj = ActiveDocument.GetSheetObject("CH01")
obj.ExportBiff "c:\temp_del\DataM1023.xls"  '暫存的excel 檔案
    'MsgBox("Export done")
 End Sub





Sub mailM1023
Dim objEmail

Const cdoSendUsingPort = 2 ' Send the message using SMTP
Const cdoBasicAuth = 1     ' Clear-text authentication
Const cdoTimeout = 60       ' Timeout for SMTP in seconds

     mailServer = "yourMailserver" ' inpurt your MailServer
     SMTPport = 25
     mailusername = "yourMailUser" ' inpurt your MailServer user 認證用
     mailpassword = "Password"     ' inpurt your Mail  user password 認證用

     mailto = "xxx@mail.com"  ' inpurt who you want to email
   
     mailSubject = "QlikView mailSubject"  ' inpurt mailSubject
       mailBody = "***** This is an automated message from qlikview  ******"  & Chr(13) & Chr(10)
   
Set objEmail = CreateObject("CDO.Message")
Set objConf = objEmail.Configuration
Set objFlds = objConf.Fields

With objFlds
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = mailServer
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPport
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = cdoTimeout
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasicAuth
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = mailusername
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = mailpassword
    .Update
End With

objEmail.To = mailto
objEmail.From = mailusername
objEmail.Subject = mailSubject
objEmail.TextBody = mailBody
objEmail.AddAttachment "C:\temp_del\DataM1023.xls" 'mail 出暫存的excel 檔案
objEmail.Send

'MsgBox("Message Sent")
Set objFlds = Nothing
Set objConf = Nothing
Set objEmail = Nothing

ActiveDocument.Save  
ActiveDocument.GetApplication.Quit   

END Sub

 其它類似語法  滙出成TXT 檔 export txt file format from MARCO

QlikView 語法攻略- 簡易歸納WebLog 分析

剛好手上一個例子..分享給各位參考,

其需求是要將網路上的ＵＲＬ連結歸類屬性，
一般會利用網址中的特別路徑或是參數來判別。

資料需求會有兩項，一個表格為關鍵字，另一個表格則是weblog
Mapping String 就是關鍵字, 用你的關鍵字去歸類資料

範例下載

QlikView 的超強 Data Modeling

QlikView 超強的 Data Modeling

話說一般BI 大都是要先在資料庫端處理好資料，及整理資料後才能介接到BI 上將資料抓出分析,現在的工具甚至還能同時混用異質性資料，但是要每個異質資料都配合你所要要的資料格式給你分析實在是不太可能，所以還是得認份的自己處理資料的！！

簡單來介紹幾個在語法裡用到的像是：

• resident：先前載入的表格載入資料　重覆拿下來進行運算或是其它資料處理；若資料只想保留運算後的你還能利用Drop Table xxxname;方式來刪除表資料。　

1| tab1:

　Load * ;
   SQL Select A,B,C,D from transtable;

2| Load A,B,month(C),A*B+D as E resident tab1;　

3| Load A,A+B+C resident tab1 where A>B;

4| Load A,B*C as E resident tab1 order by A;

5| Load A,B*C as E resident tab1 order by 1,2;

6| Load A,B*C as E resident tab1 order by C desc, B asc, 1 desc;

• 載入不在檔案上的資料

Load * Inline

  [CatID, Category

   0,  Regular

   1,   Occasional

  2,   Permanent];

• 將資料分組

用來試先先運算資料，來儲存

Load ArtNo, round(Sum(TransAmount),0.05) as ArtNoTotal from table.csv group by ArtNo;

Load Week, ArtNo, round(Avg(TransAmount),0.05) as WeekArtNoAverages from table.csv group by Week, ArtNo;

以上先初步列出幾組，但其實還有很多應用，例如使用Web Log ,等LOG 資料的載取,

都是可以在QV 用一套工具就可完成處理資料到資料分析的功能。

QlikView 超強的 Data Modeling- Crosstable精靈教你如何轉換表格

你是不是也有下圖這類型表格資料，尤其在做年度預算時更是常常出現；這類的資料在ＱＶ裡就像是＂一塊小蛋糕＂，我們可以利用精靈的方式就可以將資料滙入囉！一點都不需要在次資料表格的加工

=====================================================================

步驟說明：

　

選擇table files點選你的檔案，接著點選下一步

你會發現這張報表有些表頭資料是我們不需要的，你可以選擇＂Delete Marked" ，但是考量到以後我可能有重覆的資料要滙入所以還是套用判斷式比較好，接下來我們使用此方法來示範

選擇conditional deleted ，注意我下圖中有圈選欄１這是我拿來判斷，當欄１有空值時就刪除不要滙入資料

按ＯＫ　後就會出現像下圖一樣，表頭空白的資料不見了。

接著下一次，我們來處理交叉表格的處理

首先，我們先訂義Label ，再點選Cosstable 

出現此畫面時，就是在定義你的横軸資料跟原本的維度資料，

紅色的地方指的就是不需要轉換方向的欄位

綠色指的是要轉換方向的維度，並且可以重新命名為"Month"

藍色指的是資料值，重新命名為"AMT" 然後確定

看到了嗎，資料已經從橫向變成直的囉!

如何加快QlikView速度及效能-1

如何加快QlikView速度及效能

• Allow Minimize  縮小時QV 不會運算,當打開來時才會運算

•  Calculation Condition  如果條件不符合時,則資料不會運算,且資料內容可以呈現ERROR 提示訊息

• Data Model Scenarios and Solutions 

• Complex Dimensions and Expressions  盡量減少複雜的維度計算，及Count() 方式,

如果要減少COUNT() 你可以在Script語法裡增加一個虛欄位OrderRecordCounter , 例如 :
LOAD
OrderID,
1 as OrderRecordCounter
Resident Facts;

之後要統計時只需要用SUM(OrderRecordCounter)即可

QlikView 語法攻略- 列出維度欄位控制選擇條件

最近在國外BLOG 看到一個範例與大家分享
這個範例原本是在說明另一種使用Chart Object 呈現像multibox選擇條件的方式
但是其中使用了一項控制方式特別拿出來跟大家說明一下
因為這樣的應用再進階一點可以控制到使用維度的互動，或是User 可用的維度呈現控制

箭頭下方的Multibox Fields 即為維度清單列表, 使用者勾選了那些表格則會在上方呈現欄位供使用者條件選擇.接下來我們來拆解這個小丿步吧!


首先  1: 在語法裡必需要新增一個自定的維度清單TABLE
Multibox:
LOAD * INLINE
[
%Multibox
Year,
Name,
City,
Country,
Product,
Salesperson
];

而Multibox Fields  就是拿取%Multibox 欄位利用List Box 來呈現

動作 2:  建立一變數名稱為eMultiboxShow
         公式:   Index('|' & Concat(%Multibox, '|') &   '|', '|' & $1 & '|') > 0

         Concat(%Multibox, '|')  指是的選擇欄位的合併, 例如: 選了year , name
        則資料的組成就會變為   year|name
        那整個公式的測試請改為下列 , 例如 'Year'   ; 有發現嗎原本公式是$1 但現在用'Year'取代         了, 這個的意思是$1是傳入的參數的第一位,只是我們現在直接用Year 來使用  
  　   *替代語法*  Index('|' & Concat(%Multibox, '|') &   '|', '|' & 'Year' & '|') 

      {Examples index() 找出字串位置 :  
                            index( 'abcdefg', 'cd' ) returns 3

                  index( 'abcdabcd', 'b', 2 ) returns 6

                            index( 'abcdabcd', 'b', -2 ) returns 2
     }
    運用了Index() 查字串的特性,所以會得到>0 的位置

動作 3: 啓動條件  在Chart Object
       
             在啟用條件式用填入 $(eMultiboxShow('Year'))
              $() 是使用變數的意思，eMultiboxShow　是我們剛剛設的變數名稱，需要傳入值來取代$1 這數是傳用'Year'  這個就會和*替代語法*一樣了，　有發現嗎

以上很簡單的方式就能控制條件了。
而原本的Multibox 改進的方式可參考國外網站

Document Chaining - 檔案鏈接 大資料量分析方式之一

其實我們大部份的分析行為都是針對統計運算後的數值來解析, 但是
實際上我們都是利用明細資料來運算出來的, 也因為如此以前的OLAP 方式就會變成效能愈來愈差的狀況,但實際上我們應當要將資料的屬性切分開來,怎麼說呢?
其實就是引導使用者先以高視角的方式分析出資料,找出自己想看的資料區間後,再引導使用者查看明細資料,這樣的做法可以加快且有效率的讓使用者可以快速分析問題, 又不用一開始就開一個很大的明細檔來分析。

這方式簡單來說就是分析像EXCEL 的樞紐分析, 點選要的分析區域後再查看明細.

那在QlikView 的執行方式是什麼呢?
我們可以利用類似的方法叫做"Document Chaining"

會有一個事先滙總的分析資料, 利用維度條件串接明細資料條件
如下圖, 左方是一個滙總的分析,其維度有日期及地點,
右方是一個明細資料,其維度有日期及地點,
各位有發現了嗎?    沒錯,就是會相同的維度分析資料
也就是利用這個方式, 使用者可以快速分析又快速查詢資料

實現方法:
先製做兩個APP , 一個為匯總資料, 一個為明細資料 , 其中都有共用的維度
在滙總的APP 裡建立一個按鈕, 這按鈕是USER 確定要查詢明細時使用,

建立Actions ,

QlikView 語法攻略 - ValueList

ValueList

返回一個set資料，呈現於計算中的維度。
valuelist函數可以創建一個維度資料的圖表（Chart object)，可以通過重述與圖表表達的相同參數的valuelist函數來引用對應於一個特定的表達的圖表值。

所以不是資料一定都要用Inline 寫在Script 裡，若是臨時用表或是一次性使用的維度都可以運用此函數。

使用方式 :
下方圖例就是利用valuelist 來產生一個陣列的表

下載範例: http://community.qlik.com/docs/DOC-5011

QlikView 語法攻略 - Aggr 進階彙總函數

Aggr 進階彙總函數

其實這個函數用很簡單的比喻就是像是SQL 的Group By 的感覺,舉個例子:


 原始資料的列表:

Count 各Sales 有多少個客戶

Aggr彙總出上一步驟中Count 各Sales 有多少個客戶的值，所以下表即呈現1,2,3,5

而解釋下表就是;

賣出一個客戶的sales 有2個,賣出2個客戶的Sales 有1個

以上大家應該可以有解單的了解了，接下來我們來進階挑戰一下：

問題一，　如果我的欄位是CycleGroup　或是Drill-down 欄位呢, 那怎麼寫這變動的欄位呢?
解答: 
----------------------------------------------------------------------------------------------------------------------------
將欄位改為[$(=GetCurrentField(CycleGroup))] 即可
Aggr(count(Customer), [$(=GetCurrentField(CycleGroup))])

問題二 ，　我不知道客戶是選擇了什麼欄位，如何變動判斷？
解答:
-------------------------------------------------------------------------------------------------------------------------------
此時我會建議大家利用字串組合的方式來達成這個目的,
如: 建立一個變數
變數 v_DimStr= if(GetSelectedCount(SalesRep)>0,', SalesRep')&if(GetSelectedCount(Customer)>0,', Customer' ) 
修改語法變成 字串組合方式如下:
=$(='Aggr( count(Customer) ' & v_DimStr &' )'  )
特別注意:$(=’’) 是字串組合的意思

如此一來就能彈性動態的Aggr 進階彙總囉!!

什麼是Partial Reload 如何部份更新資料?

你有多少龐大的資料需要每天,每小時更新一次?

或者是當日資料的修改資料要定時的上傳分析?

Partial Reload 允許的QlikView應用時，添加或更換的資料表到資料模型裡，同時保留現有的表不會受到影響。由於選擇更換的資料表資料量已經變少了，所以更能Reload重新加載變得更快。

如何讓Partial Reload生效，最重要的就是要在語法裡增加前置提示  Replace 或 Add

例如:

  Table1:

  LOAD s_store_id, s_store_id From store.csv;                            //(這是正常的 relaod)

  Add LOAD s_store_id, s_store_id From Newstore.csv;            //(這是 Partial reload)

Table1:

   LOAD s_store_id, s_store_id From store.csv;                          //(這是正常的 relaod)

   Replace LOAD s_store_id, s_store_id From Newstore.csv;     //(這是 Partial reload)

正常的 relaod - 第一語句將執行產生Table1, 但立刻被第二句取而代之的是 Newstore 的內容但是表的名稱是 Table1;
Partial reload- 第一語句不會被執行, 但立刻被drop掉,第二句取而代之的是 Newstore的內容但是表的名稱是 Table1

下圖為資料表 store.csv     

下圖為資料表 Newstore.csv

執行後的結果為:

s_store_sk = 1 的資料s_store_id 變成test 了 , 第15,16的新資料也進來了

範例下載Sample 請按此

在我的範例中有提供三種不同的更新語法可以參考哦!!

QlikView 也能套印報表 ( How to setting a official report in QlikView )

除了彈性互動的分析之外，QlikView也可以產生公司套表的報表格式，例如有表頭的公司名稱啦，及表尾的簽名欄；而且設計這些Reports一點都不難，只要掌握幾個技巧你也可以輕鬆的設定報表，並可以定時的產品PDF 檔案出來。

• 編輯報表，新增一張報表

• 報表設定 , 用來調整紙張邊界，頁首頁尾的文字

• 頁面設定 , 所有控制頁面內容設計 (包含橫向還是直向紙張，長表格資料跨頁以及頁首，頁尾的說明文字)

＊資料表格會跨頁時請選　＂多選頁＂

＊　若資料要以某個主題作為篩選分頁時，請勾選下列

＊　若資料有前後頁說明，或是你要Show出篩選分頁的內容時，可以將簡介高度設為５％左右，再將分頁的欄位拉至此空間裡就可以達到每一頁分頁時都可以看到是以那個資料作為篩選分頁的內容。

重新用顏色視別設定的區域圖

接著我們就可以來列印看看囉 :) 

將整理好的資料排程滙出成TXT 檔 export txt file format from MARCO

如何將整理好的資料排程滙出成TXT 檔

步驟1:
Edit Module  ( Ctrl+M )  , 增加下列語法
/*===============================
Sub exp()

Dim strNow, strDD, strMM, strYYYY, strFulldate
strYYYY = DatePart("yyyy",Now())
strMM = Right("0" & DatePart("m",Now()),2)
strDD = Right("0" & DatePart("d",Now()),2)
fulldate = strYYYY & strMM & strDD

set obj = ActiveDocument.GetSheetObject("CH06")

obj.Export "C:\ProgramData\QlikTech\Documents\DataName"&fulldate&".txt","|" 
ActiveDocument.Save
Application.Quit
End Sub

/*===============================
說明:  CH06 是你要滙出資料表的ObjectID 
obj.Export 後為檔名而 "|" 為輸出時的欄位間隔符號

步驟2:

           加入一個OnPostReload 的Trigger ;

            下列圖中我有先指定只要前一天的資料條件，再執行Marco

以上存好後再用command 的方式執行就會自動出現滙出的TXT 檔案囉.

QlikView 語法攻略 - 改變用IF 換成用Pick + Match

每次在寫資料計算判斷時寫了一大堆的IF 都讓我不知道該怎麼看語法到底對應到那個去了,

就想到在SQL 語句裡常寫的CASE....WHEN.....END 方式;

我們先來看之前的笨笨寫法如下.

if(status=A,green,

         if(status=B,Amber,

                  if(status=C,Red,Grey))).

看到了嗎,上頭一堆IF 的判斷，再亂一點我就看不出來是在true 還是false 的條件了

所以我改了寫法如下

pick( match(status, 'A', 'B', 'C')+1, 

         'Grey'       ,

         'green'     ,

         'Amber'    ,

         'Red'

      )

說明: status 是欄位; 判斷欄位內容中是'A' 還是'B'還是'C' , 而+1 是為了其它值使用.

QlikView - Set Analysis 日期時間分析

常常寫到要做日期期間比較的分析，整理一下公式如下:

上月

sum({$<Year={'$(=Year(AddMonths(Max(OrderTime),-1)))'}

    ,Month={'$(=Month(AddMonths(max(OrderTime),-1)))'}

    ,[Shop Name]=>} SalesAmt)

　

說明:

OrderTime 是一日期欄位

Max(OrderTime) 是取得目前條件日期選擇中最大的日期

Month(AddMonths(max(OrderTime),-1) 取得少一個月的月份資料;利用此方式才能真正抓到１月份時的上個月是１２月份資料。

指定月份期間 　　

Sum({<年, 月,Date={'>=$(=AddMonths(Date, -1 ) )<=$(=AddMonths(Date,-1))'}>}SalesAmt)

上年同月

sum({$<Year={'$(=Max(Year)-1)'} , [Shop Name]=>}  SalesAmt)

去年

Sum({$<Year={$(=Max(Year)-1)}>}  SalesAmt) 

本月(止) 可用為YTM

sum({$<Month={">0<$(=max(Month)+1)"}  ,[Shop Name]=> } SalesAmt)

Command line execute Reload / partial Reload 指令更新資料

個人版也可以自動化的重新更新資料!

最近有需要寫到Batch 批次檔執行的動作，剛好有也和大家來分享，如何利用指令的方式讓QV執行Reload

Command Reload
"C:\Program Files\QlikView\QV.exe" /r  "documentName.qvw"


Partial Reload
"C:\Program Files\QlikView\QV.exe" /rp " documentName.qvw"

@rem * Command Line Arguments (QVCMD) *

@rem ********************************************

@rem * /r  = Open, Reload, Save, Close *

@rem * /rp = Open, Partial Reload, Save, Close *

@rem * /l  = Open, Reload *

@rem * /p  = Open, Partial Reload *

@rem * /v  = Pass Variables eg /vBatchFlag=1 *

@rem ********************************************

And-Mode:How to find your customer 原來QlikView 不是只有綠色 也有紅色

QlikView 本身有綠白灰三個顏色，分別代表是＂選擇，答案及非關連＂
其實，QlikView 也有紅色的耶

上圖的例子綠色表示And　紅色表示Not ;
意境是 : 找出我的客戶群中有買Atles Lussekofta , Basket Shoes 產品，但是沒有買 Cap的客戶是誰？　

建立一個table ,串接資料將客戶對應多筆產品的資料組成一個新表格
andModeJoin:
Load CustomerID, Customer Resident Customers;
Join (andModeJoin)LOAD OrderID, CustomerID Resident Order_Header;
Join (andModeJoin)LOAD ProductID, OrderID Resident Order_Details;
Join (andModeJoin)LOAD ProductID, ProductName Resident Products;

這表格的長像如下:
customer01 product1
customer01 product2
customer02 product1....

這是直接應用在同一個欄位上的方式，其實ＱＶ也有另種方式，是利用Alternate 方式來讓前台人可以分析兩個相同欄位，但是用Alternate 沒有And 的關係他是OR 的方式；
意境是 : 找出我的客戶群中有買Atles Lussekofta 或是 Basket Shoes 產品的客戶中，但是沒有買 Cap的特定客戶是誰？

命名一個Excluding  Alternate : 在document 屬性設定>一般 > Alternate
建立一個Chart , 其運算式為
sum({$<ProductName = p({$} ProductName) - p({[Excluding]} ProductName)>} UnitPrice)

Naming your field names in QlikView 取一個你看的懂的欄位名稱

擁有一個乾淨友好的使用者介面看得懂的欄位名稱，是一套針對使用者操作的一個關鍵。

在分析資料時，往往在資料庫裡的資料欄位跟前端拉取資料命名會不太一樣，如果要前端看的懂的欄位名稱在QlikView 中欄位別名，必需要使用 "as" 來重新命名，如下圖例子：

在前端取德欄位會像這樣

那如果都我要用中文欄位呢？都取別名實在太辛苦了，萬一我的資料源來及欄位很多時，這種做法實在是太不科學了。

接下來就是要教各位一個小小的技巧，讓我們可以方便取名
Step 1:  利用一個List box 將所有欄位滙出來
             請選擇系統欄位$Field
             列出資料後,直接滙出到Excel

Step 2:  利用剛滙出的EXCEL 檔案，增加一個欄位為使用者識別的欄位

Step 3: 回到Script 編輯器中，將剛那個EXCEL檔案滙入

           使用MAPPING LOAD statement 於語法編輯器

Step 4: Applying the field name map

            請在語法的最後加入一行，Using 後的就是剛滙入的表格名稱

      RENAME FIELDS USING FieldMap;

*在這裡有一個要注意的地方，如果在步驟３所Mapping 的欄位沒有包含全部欄位時，則欄位名稱會以舊的為主

Step 5: 重新Reload 資料後，剛你所設定的欄位名稱就出來囉．

QlikView 11 SR6 繁體中文版快要出版囉

台灣的冰友啊~~QlikView 11 SR6 繁體中文版快要出版囉
有幸參與SR6版本的測試計劃，想起前一陣子一堆英翻中的文件要檢查，此時看到這個成果時，頓時好有成就感。
未來我們可以有繁體的Desktop 工具使用囉!  敬請期待呦!!

update . 中文版已經出來囉, 目前到QlikView 11 SR10

如何快速的連結資料到QlikView

我自己錄了段影片, 將資料儲存於Access 的交易資料，及主檔資料分別來自於csv file 及execl 檔案；將三種不同類型來源的資料滙入到QlikView 中。
此段影片雖然沒有出現我的聲音，但是已經可以學會如何滙資料的動作囉！



在連結ACCESS 資料庫時,要特別注意你的OFFICE 是什麼位元版本的;如果是使用32位元
請勾選強制32位元

如果要連結ORACLE 時，建議安裝使用Oracle Client Driver的 OLE DB
但使用時需特別先進行Oracle Client 的TNSName 名稱指定.