Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Creator III
Creator III

Re: Load column if exists

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

Highlighted
Creator III
Creator III

Re: Load column if exists

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

Highlighted
Creator
Creator

Re: Load column if exists

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
Highlighted
Creator III
Creator III

Re: Load column if exists

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;

Highlighted
Creator
Creator

Re: Load column if exists

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;

Highlighted
Creator III
Creator III

Re: Load column if exists

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

Highlighted
Creator
Creator

Re: Load column if exists

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

Highlighted
Creator III
Creator III

Re: Load column if exists

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

Highlighted
Creator
Creator

Re: Load column if exists

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.

Creator III
Creator III

Re: Load column if exists

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?

 

Highlighted
Creator III
Creator III

Re: Load column if exists

try changing this as well

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

Highlighted
Creator
Creator

Re: Load column if exists

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.