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: 
Ron1
Partner - Creator
Partner - Creator

QlikSense Conditional Code

I have below qvd files in my library location. I only want to load data from 2018_05 to rest all. How to transform my existing code(Given below)?

AUXFILE_2018_03.qvd
AUXFILE_2018_04.qvd
AUXFILE_2018_05.qvd
AUXFILE_2018_06.qvd
AUXFILE_2018_07.qvd
AUXFILE_2018_08.qvd
AUXFILE_2018_09.qvd
AUXFILE_2018_10.qvd
AUXFILE_2018_11.qvd
AUXFILE_2018_12.qvd
AUXFILE_2019_01.qvd
AUXFILE_2019_02.qvd
AUXFILE_2019_03.qvd
AUXFILE_2019_04.qvd
AUXFILE_2019_05.qvd
AUXFILE_2019_06.qvd

****My Code****

Set vStartYear = 2018;
Let vCurrentYear= year(now());
For vYear = $(vStartYear) to $(vCurrentYear)

DTA:

LOAD
ID,
Region,
Country

FROM [lib://CID/AUXFILE_$(vYear)*.qvd]
(qvd);

store DTA into [lib://STR/DTA_$(vYear).qvd));

next vYear

1 Solution

Accepted Solutions
StarinieriG
Partner - Specialist
Partner - Specialist

Hi,

maybe something like this could work:

Set vStartYear = 2018;
Set vStartMonth = 5;
Let vCurrentYear= Year(Now());

Let vStartQvd = Date(MakeDate($(vStartYear),$(vStartMonth)),'YYYYMM');

FOR Each File in FileList('[lib://CID/AUXFILE_*.qvd]')

TABLE_temp:
LOAD
SubField(Mid('$(File)',Index('$(File)','AUXFILE_')),'_',2) &
Left(SubField(Mid('$(File)',Index('$(File)','AUXFILE_')),'_',3),2) as YearMonth,
SubField(Mid('$(File)',Index('$(File)','AUXFILE_')),'_',2) as Year,
'$(File)' as File
AutoGenerate 1;

NEXT

For vYear = '$(vStartYear)' to '$(vCurrentYear)'

NoConcatenate
TABLE_$(vYear):
LOAD File
Resident TABLE_temp
Where YearMonth>$(vStartQvd) and Year=$(vYear);

FOR i=0 to NoOfRows('TABLE_$(vYear)')-1

LET vQvd = Peek('File',$(i),'TABLE_$(vYear)');

DTA_$(vYear):
LOAD
ID,
Region,
Country
From $(vQvd) (qvd);

STORE DTA_$(vYear) into DTA_$(vYear);

DROP Table DTA_$(vYear);

NEXT

NEXT

DROP Table TABLE_temp;

View solution in original post

2 Replies
Ron1
Partner - Creator
Partner - Creator
Author

Can anyone please help on this

StarinieriG
Partner - Specialist
Partner - Specialist

Hi,

maybe something like this could work:

Set vStartYear = 2018;
Set vStartMonth = 5;
Let vCurrentYear= Year(Now());

Let vStartQvd = Date(MakeDate($(vStartYear),$(vStartMonth)),'YYYYMM');

FOR Each File in FileList('[lib://CID/AUXFILE_*.qvd]')

TABLE_temp:
LOAD
SubField(Mid('$(File)',Index('$(File)','AUXFILE_')),'_',2) &
Left(SubField(Mid('$(File)',Index('$(File)','AUXFILE_')),'_',3),2) as YearMonth,
SubField(Mid('$(File)',Index('$(File)','AUXFILE_')),'_',2) as Year,
'$(File)' as File
AutoGenerate 1;

NEXT

For vYear = '$(vStartYear)' to '$(vCurrentYear)'

NoConcatenate
TABLE_$(vYear):
LOAD File
Resident TABLE_temp
Where YearMonth>$(vStartQvd) and Year=$(vYear);

FOR i=0 to NoOfRows('TABLE_$(vYear)')-1

LET vQvd = Peek('File',$(i),'TABLE_$(vYear)');

DTA_$(vYear):
LOAD
ID,
Region,
Country
From $(vQvd) (qvd);

STORE DTA_$(vYear) into DTA_$(vYear);

DROP Table DTA_$(vYear);

NEXT

NEXT

DROP Table TABLE_temp;