topic Re: Help with load script in QlikView

Help with load script

hopkinsc — Mon, 11 Jul 2011 12:16:15 GMT

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

hopkinsc — Mon, 11 Jul 2011 14:00:42 GMT

Afternoon all. A

Anyone have any ideas on my problem?

Help with load script

erichshiino — Mon, 11 Jul 2011 14:15:14 GMT

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

hopkinsc — Mon, 11 Jul 2011 15:29:35 GMT

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 — Mon, 11 Jul 2011 15:38:35 GMT

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

Re: Help with load script

hopkinsc — Tue, 12 Jul 2011 11:26:53 GMT

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 — Tue, 12 Jul 2011 11:53:42 GMT

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

hopkinsc — Tue, 12 Jul 2011 12:40:53 GMT

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 — Tue, 12 Jul 2011 15:14:12 GMT

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

hopkinsc — Wed, 13 Jul 2011 08:43:28 GMT

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

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 — Wed, 13 Jul 2011 09:54:51 GMT

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

hopkinsc — Wed, 13 Jul 2011 11:45:01 GMT

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

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

hopkinsc — Wed, 13 Jul 2011 11:52:34 GMT

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 — Wed, 13 Jul 2011 12:01:20 GMT

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

hopkinsc — Wed, 13 Jul 2011 13:22:56 GMT

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