14 Replies Latest reply: Jul 13, 2011 9:22 AM by Chris Hopkins RSS

    Help with load script

    Chris Hopkins

      Hi, in my load script i am creating a new field using wildmatch...

       

      SET cFileName = $(vcRetailQVD)StoreStockMovement.qvd;

      IF QvdCreateTime('$(cFileName)') >= 0 THEN

      StoreStockMovement:

        concatenate LOAD 

            StoreNumber,

            StoreDate,

            [Stock Movement Reason Code],

             EANCode AS EAN,

            Narrative,

            StockQty,

            StockValue,

            LinesAffected,

            RSPValueDiff,

            CostValueDiff,

            StoreTime,

            %LkStoreReasonCode,

            if(wildmatch(Narrative,'Goods Rec*')>0,'Goods Received/Returned',

            if(wildmatch(Narrative,'Stock*')>0,'Stock Checking Variances')) AS [Stock Reason1]     

           

      FROM $(cFileName) (qvd);

       

      and in another table i am renaming a field :

       

      SET cFileName = $(vcRetailApplicationResource)StoreReasonCode.csv;

      IF FileTime('$(cFileName)') >= 0 THEN

      StoreReasonCode:

           LOAD

           @2 AS [Stock Movement Reason Code],

           @3 AS ReasonDescription,

           @5 AS [Stock Reason],

           @3 & ' - ' & @2 AS [Stock Adj Reason]

          

      FROM [$(vcRetailApplicationResource)StoreReasonCode.csv]

      (txt, codepage is 1252, no labels, delimiter is ',', msq);

      EndIf

       

      What i want to do is combine the 2 fields into one

      Stock Reason1

      Stock Reason

       

      Can anyone help me with this please?

       

      Thanks

       

      Chris

        • Help with load script
          Chris Hopkins

          Afternoon all. A

          Anyone have any ideas on my problem?

            • Help with load script
              Erich Shiino

              Hi,

              Can you provide us with some data samples, so we can understand what is wrong?

              Maybe in the first table you should rename the field to [Stock Reason] instead of [Stock Reason1]   ... but I'm not sure if that's the problem....

                • Re: Help with load script
                  Chris Hopkins

                  Hi, no, thats the first thing i tried. When i do that it combines the tables but i only get data relations for the wildmatch fields.

                   

                  I just want to join both fields together but obviously i want the table relations to work also.

                   

                  I have attached a sample of what i want. Basically in the example i  want to combine :

                  StoreReason

                  Reason

                   

                  I have attached 2 csv's which need to be saved on the root c:\

                  Thanks

                    • Re: Help with load script
                      Miguel Angel Baeyens de Arce

                      Hi,

                       

                      My guess is that you want to do a mapping when of some values when loading from one table based on the other. Something like

                       

                      stockreasonMap:
                      MAPPING LOAD @2 AS ReasonCode, 
                           @1 As Reason
                      FROM
                      [c:\stockreason.csv]
                      (txt, codepage is 1252, no labels, delimiter is ',', msq);
                      
                      storereasoncode:
                      LOAD @1 AS StoreNumber, 
                           @2 AS ReasonCode, 
                           @3 AS Narrative, 
                           @4 AS Date, 
                           ApplyMap('stockreasonMap', @2, 'No Reason') AS StoreReason // "No Reason" should no code has correspondence in the previous table
                      FROM
                      [c:\storereasoncode.csv]
                      (txt, codepage is 1252, no labels, delimiter is ',', msq);
                      

                       

                      This way the result is only one denormalized table that stores both code and name of the reason, but the second table loads the StoreReason according to the ReasonCode.

                       

                      Hope that helps.

                       

                      Miguel Angel Baeyens

                      BI Consultant

                      Comex Grupo Ibérica

                        • Re: Help with load script
                          Chris Hopkins

                          Hi Miguel, in your example, there is no reference to field @5 on the table Store ReasonCode..

                          Is there any chance you could post an example using the qvw i have attached please?

                            • Re: Help with load script
                              Miguel Angel Baeyens de Arce

                              Hi,

                               

                              I can try later on today, anyway, just replace your script but the one above to see what I say. You may mean joining instead of apply maps or something?

                               

                              It would be easier if you post an example of what you already have and what you expect from the load.


                              Regards.

                               

                              Miguel Angel Baeyens

                              BI Consultant

                              Comex Grupo Ibérica

                                • Re: Help with load script
                                  Chris Hopkins

                                  Thanks for your help...

                                   

                                  I think your script worked, it looked a bit confusing but i think that was because i was only working with those test spreadsheets which had none of my data in.

                                   

                                  This is my ACTUAL script.

                                   

                                  SET cFileName = $(vcRetailQVD)StoreStockMovement.qvd;
                                  IF QvdCreateTime('$(cFileName)') >= 0 THEN
                                  StoreStockMovement:
                                    concatenate LOAD 
                                        StoreNumber,
                                        StoreDate,
                                        [Stock Movement Reason Code],
                                         EANCode AS EAN,
                                        Narrative,
                                        StockQty,
                                        StockValue,
                                        LinesAffected,
                                        RSPValueDiff,
                                        CostValueDiff,
                                        StoreTime,
                                        %LkStoreReasonCode,
                                        if(wildmatch(Narrative,'Goods Rec*')>0,'Goods Received/Returned',
                                        if(wildmatch(Narrative,'Stock*')>0,'Stock Checking Variances')) AS [Stock Reason1]     
                                       
                                  FROM $(cFileName) (qvd);

                                  ENDIF

                                  SET cFileName = $(vcRetailApplicationResource)StoreReasonCode.csv;
                                  IF FileTime('$(cFileName)') >= 0 THEN
                                  StoreReasonCode:
                                       LOAD
                                       @2 AS [Stock Movement Reason Code],
                                       @3 AS ReasonDescription,
                                       @5 AS [Stock Reason],
                                       @3 & ' - ' & @2 AS [Stock Adj Reason]
                                      
                                  FROM [$(vcRetailApplicationResource)StoreReasonCode.csv]
                                  (txt, codepage is 1252, no labels, delimiter is ',', msq);
                                  EndIf

                                   

                                  I want to add the 2 BOLD fields together, (StockReason1 and [Stock Reason])

                                   

                                  I am having trouble incorporating your script example into my actual script. It doesn't like the fact i am using CONCATINATE in my load and also where your script says:

                                   

                                  ApplyMap('stockreasonMap', @2, 'No Reason') AS StoreReason

                                   

                                  @2 being the field from the mappings table, how do i tell it that the field i want to use is my wildmatch function?

                                   

                                  Thanks

                                   

                                   


                                    • Re: Help with load script
                                      Miguel Angel Baeyens de Arce

                                      Hi,

                                       

                                      Mapping tables are used in combination with the ApplyMap function. A mapping table loads always and only two fields, the first of which is used as a parameter in the ApplyMap function. When the ApplyMap function is called, the first parameter is the mapping table to look into, the second parameter is the field you want to search for and the third is a fixed expression by default, when no coincidences are between both tables.

                                       

                                      In my example above, I'm using "@2" because I'm using your data to load, and "@2" is the field that stores the ReasonCode in the file.

                                       

                                      Said in different words, what I want is to say QlikView "give me the correspondence in the mapping table for the current value of the field @2, and if you don't find any value, then return the default value 'No Reason'".

                                       

                                      But I'm afraid I may be missing something here, what do you mean by "I want to add the 2 BOLD fields together, (StockReason1 and [Stock Reason])"?

                                      • Is the field @2 in your table "StoreReasonCode" (renamed as "Stock Movement Reason Code" in your script above) the same as "Stock Movement Reason Code" in the table "StoreStockMovement"?
                                      • If yes, is the field "Stock Reason1" the one you want to use in the table "StockReasonCode" instead of @5 (renamed as "Stock Reason")?

                                       

                                      If yes, you need a mapping table from the "StoreStockMovement" table having the Code and the Reason, and use the ApplyMap in the "StoreStockCode" table instead of the actual field @5 that seems to have the Reason descriptive name in that table. Is this correct?

                                       

                                      The code should then look like the following

                                       

                                      SET cFileName = $(vcRetailQVD)StoreStockMovement.qvd;
                                      
                                      
                                      IF QvdCreateTime('$(cFileName)') >= 0 THEN 
                                            StoreStockMovement:
                                            concatenate LOAD  // this CONCATENATE means that there is a table above this, is that right? 
                                                     // If not, you cannot concatenate
                                            StoreNumber, 
                                            StoreDate, 
                                            [Stock Movement Reason Code], // This is the field @2 in the StoreReasonCode table
                                             EANCode AS EAN, 
                                            Narrative,
                                            StockQty, 
                                            StockValue, 
                                            LinesAffected, 
                                            RSPValueDiff, 
                                            CostValueDiff,
                                            StoreTime, 
                                            %LkStoreReasonCode,
                                            // These values are to be set in the StoreReasonCode table
                                            if(wildmatch(Narrative,'Goods Rec*')>0,'Goods Received/Returned',
                                            if(wildmatch(Narrative,'Stock*')>0,'Stock Checking Variances')) AS [Stock Reason1]      
                                            FROM $(cFileName) (qvd);
                                      
                                      ENDIF
                                      
                                      // Here we go
                                      ReasonCodeNameMap:
                                      MAPPING LOAD [Stock Movement Reason Code],
                                           [Stock Reason1]
                                      RESIDENT StoreStockMovement; // I take the code and reason from the table above
                                      
                                      SET cFileName = $(vcRetailApplicationResource)StoreReasonCode.csv;
                                      
                                      IF FileTime('$(cFileName)') >= 0 THEN 
                                      
                                      
                                           StoreReasonCode:
                                           LOAD 
                                           @2 AS [Stock Movement Reason Code], // See comment in table StoreStockMovement 
                                           @3 AS ReasonDescription,
                                           //@5 AS [Stock Reason], // don't want this field
                                           ApplyMap('ReasonCodeNameMap', @2, 'No Reason') AS [Stock Reason], // you want this instead
                                           @3 & ' - ' & @2 AS [Stock Adj Reason]
                                           FROM [$(vcRetailApplicationResource)StoreReasonCode.csv]
                                           (txt, codepage is 1252, no labels, delimiter is ',', msq);
                                      
                                      
                                      EndIf
                                      

                                       

                                      Now you have the reason from table StoreStockMovement in table StoreReasonCode.

                                       

                                      Hope that helps

                                       

                                      Miguel Angel Baeyens

                                      BI Consultant

                                      Comex Grupo Ibérica

                                        • Re: Help with load script
                                          Chris Hopkins

                                          Hi Miguel, I tried that code and i get Table not found

                                          untitled.bmp

                                          What i mean by "I want to add the 2 BOLD fields together, (StockReason1 and [Stock Reason])" is that in my earlier post i made the fields BOLD that i want to combine.

                                           

                                          Basically all i want to do is create a new field from using an if(wildmatch function:

                                           

                                          if(wildmatch(Narrative,'Goods Rec*')>0,'Goods Received/Returned',

                                          if(wildmatch(Narrative,'Stock*')>0,'Stock Checking Variances')) AS [Stock Reason1]

                                           

                                          then add this field to another field in a different table:

                                           

                                          SET cFileName = $(vcRetailApplicationResource)StoreReasonCode.csv;

                                          IF FileTime('$(cFileName)') >= 0 THEN

                                          StoreReasonCode:

                                               LOAD

                                               @2 AS [Stock Movement Reason Code],

                                               @3 AS ReasonDescription,

                                               @5 AS [Stock Reason],

                                               @3 & ' - ' & @2 AS [Stock Adj Reason]

                                           

                                          So i would end up with another new  field which is a combination of the new wildmatch field ([Stock Reason1]) and the field from the 2nd table ([Stock Reason]).

                                           

                                          I appreciate your help, i have attached my script, but obviously you will not be able to reload it...

                                           

                                           

                                           

                                           

                                               

                                           

                                            • Re: Help with load script
                                              Miguel Angel Baeyens de Arce

                                              Hi,

                                               

                                              First of all, you are getting that error because I was loading from a table that doesn't exist. So you have to change this line in my code above

                                               

                                              RESIDENT StoreStockMovement; // I take the code and reason from the table above
                                              

                                               

                                              to this line instead

                                               

                                              RESIDENT ProductSale; // I take the code and reason from the table above
                                              

                                               

                                              that is the table storing the concatenated records.

                                               

                                              Try now and let me know if that works and returns what you expect. If not, please post what you are currently getting and what do you want to get.

                                               

                                              Hope that helps.

                                               

                                              Miguel Angel Baeyens

                                              BI Consultant

                                              Comex Grupo Ibérica

                                                • Re: Help with load script
                                                  Chris Hopkins

                                                  Thanks again. That gave me a field(Stock Reason) with just 'No Reason ' . and a field Stock Reason1 with 'Goods Received/Returned, and Stock Checking Variances)

                                                  See attached...

                                                   

                                                  untitled.bmp

                                                   

                                                  Why is there a reference to [Stock Movement Reason Code]?

                                                   

                                                  ReasonCodeNameMap:

                                                  MAPPING LOAD [Stock Movement Reason Code],

                                                       [Stock Reason1]

                                                  RESIDENT ProductSale; // I take the code and reason from the table above

                                                   

                                                  I don't understnad why this field is being used? The fields i need are Stock Reason and Stock Reason1..

                                                  Or am i missunderstanding something?

                                                   

                                                  I have attached my full app, (12mb though)

                                                    • Re: Help with load script
                                                      Chris Hopkins

                                                      Where i am getting 'No Reason' in one field, and 'Goods Received/Returned', and 'Stock Checking Variances' in another field, i would want to get a field which would have

                                                       

                                                      We are not loading in :

                                                       

                                                      //@5 AS [Stock Reason], // don't want this field

                                                      from the StoreReasonCode table

                                                       

                                                      But this field is needed. i just want the field created by using the wildmatch function in the StoreStockMovement table (ProductSale after the concatenation) added to it.

                                                       

                                                      [Stock Reason] (from StoreReasonCode) + [Stock Reason1] (from ProductSale) = Combined table

                                                        • Re: Help with load script
                                                          Miguel Angel Baeyens de Arce

                                                          Hi,

                                                           

                                                          Then modify the code so

                                                           

                                                               StoreReasonCode:
                                                               LOAD 
                                                               @2 AS [Stock Movement Reason Code], // See comment in table StoreStockMovement 
                                                               @3 AS ReasonDescription,
                                                               // next line is a new field concatenating from both tables with a blank space
                                                               @5 & ' ' & ApplyMap('ReasonCodeNameMap', @2, 'No Reason') AS [Stock Reason], // you want this instead
                                                               @3 & ' - ' & @2 AS [Stock Adj Reason]
                                                               FROM [$(vcRetailApplicationResource)StoreReasonCode.csv]
                                                               (txt, codepage is 1252, no labels, delimiter is ',', msq);
                                                          

                                                           

                                                          If "Stock Movement Reason Code" is not the field to use, you still need some key field in both tables so the proper Reason is passed from the first table to the second.

                                                           

                                                          Hope that helps.

                                                           

                                                          Miguel Angel Baeyens

                                                          BI Consultant

                                                          Comex Grupo Ibérica

                                                            • Re: Help with load script
                                                              Chris Hopkins

                                                              Hi Miguel, That still didn't do the job... I have decided that what i'm trying to do may be a bit too difficult, so instead i am going to use the WILDMATCH function for ALL reasons instead of just the 2 that i had earlier.

                                                              This should work for me but it is just a bit more messy as i need to use the Narrative field (which is basically a system generated reason for each stock movement). the problem it will cause is that some reasons have the same Narrative description. i.e.

                                                               

                                                              Refrigerator Breakdowns (which is code 36)

                                                              and

                                                              Waste - Standard (which id code 3)

                                                              will both have a Narrative of 'Wastage'

                                                               

                                                              I can use the [Stock Movement Reason Code] field to seperate the figures, it just won't be as user friendly.

                                                               

                                                              Well thanks you so much for your help. I really do appreciate it..

                                                               

                                                              Thanks

                                                               

                                                              Chris