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
Download and install Qlik Sense Desktop. Then create a new Qlik Sense application. Go into the Load Script Editor and paste the following lines after the SET lines and remember to following the instructions in the first four comments in the script:
//Create a Data connection called DataFolder - press button on the right hand side [Create new connection]
// The folder for this would be: D:\Vivek\Data\Demand Data
// Create a Data connection called SaleTargetFolder - press button on the right hand side [Create new connection]
// The folder for this would be: D:\Vivek\Data\Sale Target
LET vFileMonth = 'February2017';
LET vDataDate = '2017-02-01';
LET vStoreMonth = 'Feb17';
SET AreasToProcess = 'North','East','South','West';
// SET AreasToProcess = 'North';
/ /SET AreasToProcess = 'East';
// SET AreasToProcess = 'South';
// SET AreasToProcess = 'West';
SET North_Depots = 'NDD','NSD','PKD','SKD','SND','YVD';
SET East_Depots = 'AND','GRN','GZD','IPD','NID','NOI';
SET South_Depots = 'SVD','NND','SDD','FBD','VKD';
SET West_Depots = 'JND','WDD','NLD','PND','SBN';
Customer_ID_Mapping:
MAPPING LOAD DISTINCT
Trim(Upper(Depot_id&'|'&Customer_Name)) AS key,
Customer_Id
FROM
[Lib://DataFolder/Stage1_Customer_Master.qvd](qvd);
Item_Name_Mapping:
MAPPING LOAD DISTINCT
Upper(Item_Name) AS Item_Name,
Item_id
FROM
[Lib://DataFolder/Stage1_Item_Master.qvd](qvd);
FOR EACH vArea IN $(AreasToProcess)
FOR EACH vDepot IN $(Area)_Depots
tab1:
LOAD Zone,
Depot,
CustomerName,
Route,
Route_id,
[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
[Lib://DataFolder/$(vArea)_Sale Target $(vFileMonth).xlsx]
(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 *,
Trim(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 [Lib://SaleTargetFolder/Sale_Target_$(vArea)_$(vStoreMonth).qvd](qvd);
DROP TABLE tab4;
NEXT
Hi..Petter,
First Thank you very much to help me..
But i'm getting an error while loading data, please refer attached snapshot.
Check whether the path you provider is correct or wrong.
Path is correct.
Can you send the QVF, Please?
@Anil - Please find attached file.
This demo is not the script that I sent you and the script that generated the error in the screen shot above.
Could you provide sample xlsx-files that are necessary to test for me and the actual application?
Please find attached file as required.
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...
Many many thanks..its working !!