Creating MACRO in Qlikview

[sarvesh]

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

[petter]

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

[sarvesh]

Hi..Petter,

First Thank you very much to help me..

But i'm getting an error while loading data, please refer attached snapshot.

[Anil_Babu_Samineni]

Check whether the path you provider is correct or wrong.

[sarvesh]

Path is correct.

[Anil_Babu_Samineni]

Can you send the QVF, Please?

[sarvesh]

@Anil - Please find attached file.

[petter]

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?

[sarvesh]

Please find attached file as required.

[petter · Accepted solution]

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...

[sarvesh]

Many many thanks..its working !!