Re: [ASK] if-else looping load with multiple condi... - Qlik Community

rendiyan · ‎2016-11-30

Dear Qlik Master,

I have a user with huge data.

I have 1 transaction that i need to load partially every year.

And loaded before current year data only need to load 1 time.

So i create a looping load with multiple condition, and my script is below :

Load Script

FOR nrow = 0 TO 2

LET vYear = year(today())-$(nrow);

set vTable = F_REVBYTYPE_$(vYear);

set QVDFILE_max = $(vTable).qvd;

IF '$(vYear)'=year(today()) THEN

$(vTable):

sql select *

from abc

where abc.date=$(vYear);

STORE * from $(vTable) into $(vTable).qvd (qvd);

DROP TABLE $(vTable);

ELSEIF '$(vYear)'<>year(today()) and FileSize('$(QVDFILE_max)') = 0 THEN

sql select *

from abc

where abc.date=$(vYear);

STORE * from $(vTable) into $(vTable).qvd (qvd);

DROP TABLE $(vTable);

ENDIF;

NEXT;

But i got a problem, my load script never get to elseif condition.

Is there anything wrong with my script?

Because when i try with only if-else, it's going well.

Thank You,

Best Regards,

adamdavi3s · ‎2016-11-30

WHy do you need the elseif?

Surely you just need ELSE ?

I've not tried to use ELSEIF as a function (I nest IFs as below) so i could be wrong but is it missing an ENDIF and maybe it requires an ELSE to work?

I personally would use this and never have an issue:

IF <condition> THEN

statement1

ELSE

IF <condition2> THEN

statement2

ELSE

statement3

ENDIF

rendiyan · ‎2016-11-30

Hi Adam,

thx for the quick response.

I've tried it but it won't work, only ran the first if load script.

Script is below :

FOR nrow = 0 TO 2

LET vYear = year(today())-$(nrow);

set vTable = F_REVBYTYPE_$(vYear);

set QVDFILE_max = $(vTable).qvd;

IF '$(vYear)'= year(today()) then

$(vTable):

LOAD * INLINE [

YEAR

'$(vYear)'

];

STORE * from $(vTable) into $(vTable).qvd (qvd);

DROP TABLE $(vTable);

ELSE

IF FileSize('$(QVDFILE_max)') = 0 then

$(vTable):

LOAD * INLINE [

YEAR

'$(vYear)'

];

STORE * from $(vTable) into $(vTable).qvd (qvd);

DROP TABLE $(vTable);

ELSE

$(vTable):

LOAD * INLINE [

BEFYEARDONE

1

];

ENDIF;

NEXT;

adamdavi3s · ‎2016-11-30

Sorry your question now makes sense to me, I was looking at it incorrectly.

Your syntax seems completely fine to me, the only thing could be if the filesize test is coming up blank?

Its worth adding some TRACE functions so you can see what is going on

FOR nrow = 0 TO 2

LET vYear = year(today())-$(nrow);

set vTable = F_REVBYTYPE_$(vYear);

set QVDFILE_max = $(vTable).qvd;

TRACE $(vYear);

TRACE FileSize('$(QVDFILE_max)') ;

IF '$(vYear)'= year(today()) then

$(vTable):

LOAD * INLINE [

YEAR

'$(vYear)'

];

STORE * from $(vTable) into $(vTable).qvd (qvd);

DROP TABLE $(vTable);

ELSE

IF FileSize('$(QVDFILE_max)') = 0 then

$(vTable):

LOAD * INLINE [

YEAR

'$(vYear)'

];

STORE * from $(vTable) into $(vTable).qvd (qvd);

DROP TABLE $(vTable);

ELSE

$(vTable):

LOAD * INLINE [

BEFYEARDONE

1

];

ENDIF;

NEXT;

adamdavi3s · ‎2016-11-30

actually that is rubbish as you should still get your inline load.... maybe put the logfile on and share that so I can see what is going on?

rendiyan · ‎2016-11-30

PFA.

I've added TRACE function.

Once again, thx for the quick response

adamdavi3s · ‎2016-12-01

Perfect, yes it looks like it is the filesize it doesn't like to me... the years are switching fine but its obviously thinking the files are non existent or 0 size... I think it might be the syntax, give this a go, I have added a variable to call the filesize and then slightly changed the else which means if the file is null it will also load I believe. This obviously assumes the QVD are in the same folder as the QVW

FOR nrow = 0 TO 2

LET vYear = year(today())-$(nrow);

set vTable = F_REVBYTYPE_$(vYear);

set QVDFILE_max = $(vTable).qvd;

TRACE $(vYear);

let vFileSize = FileSize('.\'&'$(QVDFILE_max)') ;

IF '$(vYear)'= year(today()) then

$(vTable):

LOAD * INLINE [

YEAR

'$(vYear)'

];

STORE * from $(vTable) into $(vTable).qvd (qvd);

DROP TABLE $(vTable);

ELSE

IF $(vFileSize) < 1 then

$(vTable):

LOAD * INLINE [

YEAR

'$(vYear)'

];

STORE * from $(vTable) into $(vTable).qvd (qvd);

DROP TABLE $(vTable);

ELSE

$(vTable):

LOAD * INLINE [

BEFYEARDONE

1

];

ENDIF;

NEXT;

rendiyan · ‎2016-12-02

still won't work.

i dont know why.

thx anyway

adamdavi3s · ‎2016-12-05

can you share the latest log?

Maybe add a trace on filesize as well

[ASK] if-else looping load with multiple condition