topic Re: QlikSense Conditional Code in App Development

QlikSense Conditional Code

Ron1 — Thu, 17 Oct 2019 13:59:42 GMT

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

Re: QlikSense Conditional Code

Ron1 — Thu, 17 Oct 2019 18:18:02 GMT

Can anyone please help on this

Re: QlikSense Conditional Code

StarinieriG — Fri, 18 Oct 2019 12:20:15 GMT

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;

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);

DROP Table TABLE_temp;