QLIKVIEW 12 版本上架囉! 12-08

QLIKVIEW 12 OFFICIALLY AVAILABLE

DECEMBER 8, 2015 ANOH LEAVE A COMMENT

For just a few hours now, QlikView 12, the latest version of one of the world’s leading Business Intelligence platforms, has been available on the market. Besides several innovations familiar from the beta version, the software now offers additional interesting functions.

Around two months after ending the beta test phase, Qlik has published the latest release of its proven analysis solution, QlikView 12. Even before the official release, users were able to get a first glance at several of the new features and extensions. The new features include the improved QIX Engine, extended touch functions, and a new online help portal including context-sensitive help pages to provide support during product use. Furthermore, the beta version already included optimisations pertaining to the security of external interfaces.

如果已經安裝了11.2 的Desktop 版本 需移除再裝QlikView 12

而且有超讚的介紹可以滙入一些外部資料及JSON ,不用再像以前要寫語法了..果然愈來愈合適大資料Big Data 的分析世界

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 用一套工具就可完成處理資料到資料分析的功能。