Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
sarvesh
Contributor III

Creating MACRO in Qlikview

Hi..All,

Anybody help me to understand MACRO, actually i'm going to create a Macro in QlikView to create "qvd" files for Qlik Sense Server data upload.

Please guide me that how can i start working on this.

Regard's

Sarvesh Srivastava

1 Solution

Accepted Solutions
MVP
MVP

Re: Creating MACRO in Qlikview

I removed a bug in the code I had proposed. It seemed to work well with the limited test data I got from you. So see if it works for you now...

30 Replies
MVP
MVP

Re: Creating MACRO in Qlikview

Just to make it clear - you don't have to use Macros to create QVD-files. I have made a lot of load scripts and QlikView development without ever needing to use Macros. Macros however have their use - especially to automate things for a developer that is otherwise a time consuming task.

Macros are programmerd in either VBScript or JScript. VBScript is most often used and you can select which in the Module/Macros user interface. Macros can be executed either by calling Macro functions from the load script or by using them from the running QlikView application. In the first case you will not be able to use the QlikView Object Model since a load script does not connect to the running applications UI in any way. Macros in the UI of a running application on the other hand has full access to the QlikView Object Model.

Macros can only be employed and used in QlikView and are not available with any version of Qlik Sense.

This is the secion in the online help that documents how to use Automation and Macros:

http://help.qlik.com/en-US/qlikview-developer/12.1/Subsystems/Automation/Content/automation-introduc...

Why JScript is not mentioned in the online help - I have no idea why. It is an omission probably for the lack of popularity with traditional Windows programmers. JScript is however the very same Javascript that has been used for ages in all web-development contexts in Internet Explorer and other Microsoft products. So JScript can be very powerful if you already know it and you have access to a wealth of code on the internet and in books. However almost all API examples have been written in VBscript and you have to know how to translate that into VBScript if you want to use JScript.

MVP
MVP

Re: Creating MACRO in Qlikview

Do you have any more details on your idea for using the Macros and or load script to create QVDs?

sarvesh
Contributor III

Re: Creating MACRO in Qlikview

Actually the organisation where i'm working right now, some "qlikview documents" are created in "QlikView personal edition" and when we open this file and run script it generates "qvd" file and i put all these "qvds" into Qlik Sense server (Path- C:\QlikSense\SharedDataFolder) and after that run task for data uploading.

Could you explain me about this complete process and I want to create that "qlikview document".

Regard's

Sarvesh Srivastava

MVP
MVP

Re: Creating MACRO in Qlikview

You find most information online actually. It is hard for me to explain anything that is hidden inside the load script of the QlikView application you are using unless you share the load script with us.

sarvesh
Contributor III

Re: Creating MACRO in Qlikview

Please find attached file for the reference.

MVP
MVP

Re: Creating MACRO in Qlikview

This load script:

  1. Reads an Excel sheet with the name "East_Sale Target February2017.xlsx"  and all the tabs AND,GRN,GZD,IPD,NID and NOI
  2. Then it unpivots the resulting table so item values that are in separate columns in the Excel spreadsheet is transferred to a single column with multiple rows per item
  3. Customer IDs are translated into a customer names
  4. Item IDs are translated into item names
  5. The resulting single table is stored as a QVD-file named "Sale_Target_East_February2017.qvd"
  6. The last six tabs in the load script are obsolete and old code since they will not be executed because of the EXIT script in the fifth tab named "Main 10". The logic of these are being done in the "Main 2" tab obviously
  7. Note: The February2017 is set in a varialbe at the beginning of the script and will determine the Excel file being read and the resulting QVD-file being written

Most importantly there are no Macros involved in this application. Maintenance and further development has to be done in the load script.

sarvesh
Contributor III

Re: Creating MACRO in Qlikview

Thanks for reply Petter,

Its my fault i thought this is Marcos.

  Now, i'm expecting one more help..actually the file i have posted you is not able to open at my end because getting error which is mentioned below in snapshot, please help me to do the same.

Qlikview_PE_Error.jpg

Qlikview_PE_Error_2.jpg

Regard's

Sarvesh Srivastava

MVP
MVP

Re: Creating MACRO in Qlikview

You can forget about the "old" Target_Cross_East.qvw. It is correct what is stated and you can't actually use that qvw anymore. Actually you should consider using Qlik Sense instead - with minor changes you can run the same load script. With Qlik Sense Desktop you will not run into this problem in the future because QVF-files can be shared freely between different users.

Luckily for you I have a copy of the entire load script and that is all that matters. So if you create a blank new QlikView application and open the "load script editor" you can paste the script that I have attached here. After that save the app and reload. Now you have a new version of the app with the same load script that you can continue to use in the future:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LET vFileMonth = 'February2017';

LET vDataDate = '2017-02-01';

LET vStoreMonth = 'Feb17';

Customer_ID_Mapping:

MAPPING LOAD DISTINCT

  Upper(Depot_id&'|'&Customer_Name) AS key,

  Customer_Id

FROM

(qvd);

Item_Name_Mapping:

MAPPING LOAD  DISTINCT

     Upper(Item_Name) AS Item_Name,

     Item_id

FROM

(qvd);

FOR EACH vDepot IN 'AND','GRN','GZD','IPD','NID','NOI'

tab1:

LOAD Zone,

     Depot,

     Route,

     Route_id,

     CustomerName,

     [HG 700L],

     //[HG 600L],

     [HG 350L/2],

     //[HG 300H],

     BROWN400,

     [SBUN(2PC)160],

     [BBUN 250],

     [PAV 300],

     [BBUN 350],

     [SANDWICH 500],

     [WW 500],

     [WW 250L/2],

     [KULCHA 250],

     [JUMBO BROWN],

     [GARLIC 300],

     [HG 500 ATTA],

     [JUMBO 1600],

     [MULTI GRAIN 450],

     //[HG 600],

     [HG 400E],

     [HG 200E/2],

     [HG 350M],

     [FRUITY 150],

     [SB (1PC) 80],

     [HG 200M],

     [HG LONG 250],

     [BROWN (2PC) 50],

     [MILK BREAD 300GM],

     [READY ROTI 200GM],

     [PIZZA 250],

     //[PIZZA 150],

     [HG 450 W],

     [BURGER ATTA 250],

     [VITAMIN WHITE],

     [VITAMIN BROWN],

  [HG 600 E],

  [HG 300 E]

FROM

(ooxml, embedded labels, table is $(vDepot));

NEXT

tab2:

CrossTable(Item, Target, 5)

LOAD *

RESIDENT tab1;

DROP TABLE tab1;

tab3:

NOCONCATENATE

LOAD *,

  ApplyMap('Customer_ID_Mapping',key1,'N/A') AS [Cusomer ID];

LOAD *,

  Upper(Depot&'|'&CustomerName) AS key1,

  CustomerName AS [Dealer Group],

  Num('$(vDataDate)') AS link_Date

RESIDENT tab2;

DROP TABlE tab2;

tab4:

LOAD *,

  ApplyMap('Item_Name_Mapping',Item,'N/A') AS Item_Cd;

LOAD

  Zone,

     Depot,

     Route,

     [Route_id],

     If(Item ='Roti', 'READY ROTI 200GM',

      If(Item = 'Milk Bread', 'MILK BREAD 300GM',

      If(Item = 'BROWN (2PC) 50', 'BROWN (2PC)  50',

      If(Item = 'HG 200E/2', 'HG 200E/2',

      If(Item = 'HG LONG 250', 'HG LONG 250',

      If(Item = 'BBUN 250', 'BBUN  250',

      If(Item = 'SB (1PC) 80','SB (1PC)   80',

      If(Item = 'Milk BREAD','MILK BREAD 300GM',

      If(Item = 'READY ROTI','READY ROTI 200GM',

      If(Item = 'MILK BREAD','MILK BREAD 300GM',

      If(Item = 'Milk Bread 300gm','MILK BREAD 300GM',

      If(Item = 'Ready Roti 200gm','READY ROTI 200GM',

      Item)))))))))))) AS Item,

     If(Target<0,0,Target) AS Target,

     [Dealer Group],

     link_Date,

     [Cusomer ID]

RESIDENT tab3

WHERE Len(Zone)>1;

DROP TABLE tab3;

STORE tab4 INTO 'D:\Vivek\Data\Sale Target\Sale_Target_East_$(vStoreMonth).qvd'(qvd);

sarvesh
Contributor III

Re: Creating MACRO in Qlikview

Thank you so much Petter..

Can i send you three more file for the script.