If Load statement

[YanMin]

Hi, 

Can anyone help me with following if load statement. My files got different column names. So what I want to do is after certain date, I want load different column name. My codes is as follows: 

if(Date(right(FileBaseName(),8))<Date('20210405'),

"Wishlist Visitor","Wishlist Visitors") as "Wishlist Visitor",

The spelling of column name is different for the files that is before that specific date. That codes seems no work.

Can anyone help?

Thanks in advanced

[rwunderlich]

 If you can use "LOAD *" instead of listing individual fieldnames, just add an alias at the top of your script. 

Alias "Wishlist Visitors" as "Wishlist Visitor";

That's it, you're done. 

For more, see 

https://qlikviewcookbook.com/2018/12/loading-varying-column-names/

[YanMin]

Hi Sir, 

Thank you so much for help. Is this Alias only working for Load*? It seem like not working for individual field loading. 

[rwunderlich]

Yes, this Alias technique will only work for Load *.   If you must list the fieldnames, then you have to process the files in a loop and set a variable for the fieldname like:

Let  vFieldName = if(right(FileBaseName($(filename)),8)< 20210405, 'Wishlist Visitor','Wishlist Visitors');

Data:
LOAD
  ID,
  $(vFieldName),
  foo
FROM ...

-Rob

[YanMin]

Hi Sir, Thank you so much for this. But when I run that I got the following error. I paste my codes and ss of errors.

Let vFieldName1 = if(date(right(FileBaseName(),8))<date('20210405'),'Wishlist Visitors','Wishlist Visitor');
Let vFieldName2 = if(date(right(FileBaseName(),8))<date('20210405'),'Add To Cart Visitors','Add to Cart Visitors');

I am sorry, I am quite new to qliksense and not sure where the bug is. Thanks a lot for help.

[rwunderlich]

As your fieldname contains spaces, you will need to enclose it in double quotes or square brackets.

Let vFieldName1 = if(date(right(FileBaseName(),8))<date('20210405'),'"Wishlist Visitors"','"Wishlist Visitor"');

-ob

[YanMin]

Hi Sir, I just noticed that. 

But this if statement seem like no work if I put in variable name outside the Load and keep giving me for second values. 

But when I put this if statement inside the load, this 'if' function is work but still give me the error that I have faced originally. 

I am sorry for keep bothering you. Appreciate alot for your help.

Thank you.

[rwunderlich · Accepted solution]

you will need to create a "for each file in filelist()" loop.

https://help.qlik.com/en-US/sense/February2021/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptCont...

Your loop will look something like:

For each vFile in filelist([lib::/Automation.../*])

Let vFieldName1 = if(date(right(FileBaseName([$(vFile]]),8))<date('20210405'),'"Wishlist Visitors"','"Wishlist Visitor"');

LOAD
  $(vFieldName1),
   ... //your load statement
From [$vFile]...

Another perhaps simpler alternative is
1. Use the Alias and Load * technique. 
2. Load Resident the table to do your required transformations on the resident table. 
3. Drop the first table. 

Something like:

Alias "Wishlist Visitors" as "Wishlist Visitor";

Data:
LOAD 0 as DummyField AutoGenerate 0;  // For concatenation

Concatenate (Data)
LOAD * 
FROM [lib://.../*];

Data2:
NoConcatenate 
LOAD
  "Units Sold",
  "Wishlist Visitors",
  if (...., etc)
Resident Data;

Drop Table Data;

-Rob

[YanMin]

Hi Sir thank you so much. Alias method work here. I think it will be better to use Alias method as I can change other field if necessary in future. But after Load* ,I put additional field fileBase() name as I can't catch file name from resident table. So is that means that Alias can work Load* + additional field? just for knowledge.

Thank you so much anyway and appreciate a lot for your help. 

[rwunderlich]

Yes, Alias will work on *+fields. 

It does not matter if the fieldname is implicit (*) or explicit, Alias will rename.  Another nice feature of Alias is that the field does not have to exist in that script execution.

-Rob