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: 
Kunkulis
Creator
Creator

Load column if exists

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

Labels (4)
3 Solutions

Accepted Solutions
asinha1991
Creator III
Creator III

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;

 

View solution in original post

asinha1991
Creator III
Creator III

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

 

 

View solution in original post

Kunkulis
Creator
Creator
Author

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

View solution in original post

13 Replies
asinha1991
Creator III
Creator III

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;

next;

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

Kunkulis
Creator
Creator
Author

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;

asinha1991
Creator III
Creator III

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;

 

Kunkulis
Creator
Creator
Author

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

asinha1991
Creator III
Creator III

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

Kunkulis
Creator
Creator
Author

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.

asinha1991
Creator III
Creator III

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?

 

asinha1991
Creator III
Creator III

try changing this as well

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

Kunkulis
Creator
Creator
Author

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.