Load column if exists

[Kunkulis]

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

[asinha1991]

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]

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 · Accepted solution]

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]

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]

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]

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]

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]

try changing this as well

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

[Kunkulis]

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.