Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

Help with load script

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

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

View solution in original post

14 Replies
hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Afternoon all. A

Anyone have any ideas on my problem?

erichshiino
Partner - Master
Partner - Master

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

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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

Miguel_Angel_Baeyens

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

(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

(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

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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?

Miguel_Angel_Baeyens

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

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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


Miguel_Angel_Baeyens

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

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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