Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
YanMin
Contributor
Contributor

If Load statement

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

 

1 Solution

Accepted Solutions
rwunderlich
MVP & Luminary
MVP & Luminary

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

View solution in original post

10 Replies
rwunderlich
MVP & Luminary
MVP & Luminary

 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
Contributor
Contributor
Author

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
MVP & Luminary
MVP & Luminary

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
Contributor
Contributor
Author

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');

YanMin_0-1617845515879.pngYanMin_1-1617845570829.pngYanMin_2-1617845583913.png

 

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

rwunderlich
MVP & Luminary
MVP & Luminary

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
Contributor
Contributor
Author

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
MVP & Luminary
MVP & Luminary

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

View solution in original post

YanMin
Contributor
Contributor
Author

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
MVP & Luminary
MVP & Luminary

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