Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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...
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:
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.
Do you have any more details on your idea for using the Macros and or load script to create QVDs?
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
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.
Please find attached file for the reference.
This load script:
Most importantly there are no Macros involved in this application. Maintenance and further development has to be done in the load script.
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.
Regard's
Sarvesh Srivastava
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
Item_Name_Mapping:
MAPPING LOAD DISTINCT
Upper(Item_Name) AS Item_Name,
Item_id
FROM
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);
Thank you so much Petter..
Can i send you three more file for the script.