topic Re: Load column if exists in QlikView

Load column if exists

Kunkulis — Sat, 16 Nov 2024 02:16:23 GMT

Hello,

I am Loading several, same named qvds - User2019-10.qvd, User2018-10...User2013-10 etc.

Now the issue is that on the first few qvds they are lacking one column - DateOfQvd.

I was thinking maybe there is a possibility to write some ifs to see if the files date is bigger then lets say 2010-06, if it is true, than load the data that contains the DateOfQvd if not than load the data without it.

If (Righ(User2010-06,7)>2010-06 then

Load * + DatOfQvd
From User2*.qvd (qvd) else

Load* (no DateOfQvd)
From User2*.qvd (qvd) end if

Or maybe it is possible to have a check of the column count somehow?

Thank you in advanced

Re: Load column if exists

asinha1991 — Wed, 09 Oct 2019 12:56:00 GMT

there is function called qvdCreateTime, you can do something like this

for each FileExtension in 'qvd'
for each FoundFile in filelist( vSourceFolder & '\user*.' & FileExtension)

let ftime=qvdCreateTime(FoundFile);

if $(ftime)>somedate then

Load *, $(ftime) as loadtime From $(FoundFile).qvd;

else

Load * From $(FoundFile).qvd;

end if;

next;

//check for syntax and don't forget to add concatenate on top;

Re: Load column if exists

Kunkulis — Wed, 09 Oct 2019 13:11:40 GMT

I am having trouble implementing it, so this is for me from line 1:

set vSourceFolder = C:\Users\ME\Desktop\USER;
for each FileExtension in 'qvd'
for each FoundFile in filelist( vSourceFolder & '\user*.' & FileExtension)
let ftime=qvdCreateTime(FoundFile);
if $(ftime)>2013-04 then

User:
Load * (* for the sake not to write all the columns but this one has the QvdDate column in it)
FROM $(FoundFile).qvd (qvd);

ELSE

User:
Load * (Doesn't have the QvdDate)
FROM $(FoundFile).qvd (qvd);
END IF;
NEXT;
NEXT;

Re: Load column if exists

asinha1991 — Wed, 09 Oct 2019 13:27:30 GMT

some changes, let me know the exact error if it doesn't work

set vcon=;

for each FileExtension in 'qvd'
for each FoundFile in filelist( vSourceFolder & '\user*.' & FileExtension)
let ftime=qvdCreateTime(FoundFile);
if $(ftime)>date#('2013-04','yyyy-mm') then

$(vcon)

User:
Load * (* for the sake not to write all the columns but this one has the QvdDate column in it)
FROM [$(FoundFile)](qvd);

ELSE

$(vcon)

User:
Load * (Doesn't have the QvdDate)
FROM [$(FoundFile)](qvd);
END IF;

Set vcon= Concatenate;

NEXT;
NEXT;

Re: Load column if exists

Kunkulis — Wed, 09 Oct 2019 13:38:54 GMT

Pasted it the same as you showed, but I have red line under $(vcon) and the whole LOAD has the same line then as well.

I added the ';' after the $(vcon) and running it got this error (Folder name is the same as qvd):

2019-10-09 16:35:19 0002 set vSourceFolder = 'C:\Users\ME\Desktop\USER'
2019-10-09 16:35:19 0004 set vcon=
2019-10-09 16:35:19 0006 for each FileExtension in 'qvd'
2019-10-09 16:35:19 0007 for each FoundFile in filelist( vSourceFolder & '\USER*.' & FileExtension)
2019-10-09 16:35:19 0008 let ftime=qvdCreateTime(FoundFile)
2019-10-09 16:35:19 Error: Unexpected token: '08', expected one of: 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', 'like', 'and', ...

Re: Load column if exists

asinha1991 — Wed, 09 Oct 2019 13:45:06 GMT

red is ok, for some reason it shows that but it works

change it to this

let ftime=qvdCreateTime($(FoundFile))

if doesn't work

let ftime=qvdCreateTime('$(FoundFile)');

also add ---trace $(FoundFile);

to see function is fetching time

Re: Load column if exists

Kunkulis — Wed, 09 Oct 2019 13:53:40 GMT

For both didn't work, this is the let ftime=qvdCreateTime($(FoundFile)) :

2019-10-09 16:51:30 0002 set vSourceFolder = 'C:\Users\ME\Desktop\USER'
2019-10-09 16:51:30 0004 set vcon=
2019-10-09 16:51:30 0006 for each FileExtension in 'qvd'
2019-10-09 16:51:30 0007 for each FoundFile in filelist( vSourceFolder & '\USER*.' & FileExtension)
2019-10-09 16:51:30 0008 trace C:\Users\ME\Desktop\USER\USER2013-01.qvd
2019-10-09 16:51:30 0008 C:\Users\ME\Desktop\USER\USER2013-01.qvd
2019-10-09 16:51:30 0009
2019-10-09 16:51:30 Error: Unexpected token: 'qvd', expected one of: ')', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', 'like', ...
2019-10-09 16:51:31 Execution Failed
2019-10-09 16:51:31 Execution finished.

and this is ftime=qvdCreateTime('$(FoundFile)'):

2019-10-09 16:49:36 0002 set vSourceFolder = 'C:\Users\ME\Desktop\USER'
2019-10-09 16:49:36 0004 set vcon=
2019-10-09 16:49:36 0006 for each FileExtension in 'qvd'
2019-10-09 16:49:36 0007 for each FoundFile in filelist( vSourceFolder & '\USER*.' & FileExtension)
2019-10-09 16:49:36 0008 trace C:\Users\ME\Desktop\USER\USER2013-01.qvd
2019-10-09 16:49:36 0008 C:\Users\ME\Desktop\USER\USER2013-01.qvd
2019-10-09 16:49:36 0009
2019-10-09 16:49:36 0009 let ftime=qvdCreateTime('C:\Users\ME\Desktop\USER\USER2013-01.qvd')
2019-10-09 16:49:36 Error: Unexpected token: '08', expected one of: 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', 'like', 'and', ...
2019-10-09 16:49:38 Execution Failed
2019-10-09 16:49:38 Execution finished.

Re: Load column if exists

asinha1991 — Wed, 09 Oct 2019 17:40:08 GMT

let ftime=qvdCreateTime('$(FoundFile)') seems to be work better, did you put semicolon (;) after this?

if yes, can you add trace after this and share the log..i want to understand whether if condition is failing or let evaluation?

Re: Load column if exists

asinha1991 — Wed, 09 Oct 2019 20:29:45 GMT

try changing this as well

if '$(ftime)'>date#('2013-04','yyyy-mm')

Re: Load column if exists

Kunkulis — Thu, 10 Oct 2019 07:02:27 GMT

It's still failing

2019-10-10 09:56:36 0002 set vSourceFolder = 'C:\Users\ME\Desktop\USER'
2019-10-10 09:56:36 0004 set vcon=
2019-10-10 09:56:36 0006 for each FileExtension in 'qvd'
2019-10-10 09:56:36 0007 for each FoundFile in filelist( vSourceFolder & '\USER*.' & FileExtension)
2019-10-10 09:56:36 0008 trace C:\Users\ME\Desktop\USER\USER2013-01.qvd
2019-10-10 09:56:36 0008 C:\Users\ME\Desktop\USER\USER2013-01.qvd
2019-10-10 09:56:36 0009
2019-10-10 09:56:36 0009 let ftime=qvdCreateTime('C:\Users\ME\Desktop\USER\USER2013-01.qvd')
2019-10-10 09:56:36 0010 trace 2013-02-01 08:05:13
2019-10-10 09:56:36 0010 2013-02-01 08:05:13
2019-10-10 09:56:36 0011
2019-10-10 09:56:36 0011 if '2013-02-01 08:05:13'>date#('2013-04','yyyy-mm') then
2019-10-10 09:56:36 0013 User:
2019-10-10 09:56:36 0014 LOAD Date(Date,'DD/MM/YYYY')&O_SHORT_DESC as [Version],
2019-10-10 09:56:36 0015 [First Name],
2019-10-10 09:56:36 0016 [Middle Name],
2019-10-10 09:56:36 0017 [Surname],
2019-10-10 09:56:36 0018 [Email],
2019-10-10 09:56:36 0019
2019-10-10 09:56:36 0020 date(Date,'DD/MM/YYYY')&USR_ID as VersionHomeID,
2019-10-10 09:56:36 0021 [User Name]
2019-10-10 09:56:36 0022 FROM [C:\Users\ME\Desktop\USER\USER2013-01.qvd] (qvd)
2019-10-10 09:56:36 Error: Field 'Date' not found
2019-10-10 09:56:38 Execution Failed
2019-10-10 09:56:38 Execution finished.

Re: Load column if exists

asinha1991 — Thu, 10 Oct 2019 12:16:32 GMT

I see try this and I rest my case 😄 I am sure it will work now

if date#(subfield('$(ftime)',' ',1),'YYYY-MM-DD')>date#('2013-04','yyyy-mm') then

Re: Load column if exists

Kunkulis — Thu, 10 Oct 2019 13:27:59 GMT

It finally does work, but it doesn't go to the else Load where it doesn't load the QvdDate column:

2019-10-10 16:25:34 0011 if date#(subfield('2013-02-01 08:05:13',' ',1),'YYYY-MM-DD')>date#('2013-05','yyyy-mm') then
2019-10-10 16:25:34 0013 User:
2019-10-10 16:25:34 0014 LOAD Date(Date,'DD/MM/YYYY')&O_SHORT_DESC as [Version Manager],
2019-10-10 16:25:34 0015 [First Name],
2019-10-10 16:25:34 0016 [Middle Name],
2019-10-10 16:25:34 0017 [Surname],
2019-10-10 16:25:34 0018 [Email],
2019-10-10 16:25:34 0019
2019-10-10 16:25:34 0020 date(Date,'DD/MM/YYYY')&USR_HOME_ENTITY_ID as VersionID,
2019-10-10 16:25:34 0021 [User Name]
2019-10-10 16:25:34 0022 FROM [C:\Users\ME\Desktop\USER\USER2013-01.qvd] (qvd)
2019-10-10 16:25:34 Error: Field 'Date' not found
2019-10-10 16:25:36 Execution Failed
2019-10-10 16:25:36 Execution finished.

Re: Load column if exists

Kunkulis — Thu, 10 Oct 2019 13:31:36 GMT

Got it to work, changed the date# to the general date format.

if date#(subfield('$(ftime)',' ',1),'YYYY-MM-DD')>date('41365','yyyy-mm-dd') then

Re: Load column if exists

rwunderlich — Thu, 10 Oct 2019 13:51:14 GMT

Why not just use "Load *" for everything? Won't that pick up the date field if it exists?

-Rob