topic Re: [ASK] if-else looping load with multiple condition in QlikView

[ASK] if-else looping load with multiple condition

rendiyan — Wed, 30 Nov 2016 14:48:01 GMT

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;

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,

Re: [ASK] if-else looping load with multiple condition

adamdavi3s — Wed, 30 Nov 2016 15:02:05 GMT

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

Re: [ASK] if-else looping load with multiple condition

rendiyan — Wed, 30 Nov 2016 15:24:33 GMT

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

];

ENDIF;

NEXT;

Re: [ASK] if-else looping load with multiple condition

adamdavi3s — Wed, 30 Nov 2016 15:33:53 GMT

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

];

ENDIF;

NEXT;

Re: [ASK] if-else looping load with multiple condition

adamdavi3s — Wed, 30 Nov 2016 15:34:47 GMT

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?

Re: [ASK] if-else looping load with multiple condition

rendiyan — Wed, 30 Nov 2016 15:43:31 GMT

PFA.

I've added TRACE function.

Once again, thx for the quick response

Re: [ASK] if-else looping load with multiple condition

adamdavi3s — Thu, 01 Dec 2016 07:25:50 GMT

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

];

ENDIF;

NEXT;

Re: [ASK] if-else looping load with multiple condition

rendiyan — Fri, 02 Dec 2016 19:55:59 GMT

still won't work.

i dont know why.

thx anyway

Re: [ASK] if-else looping load with multiple condition

adamdavi3s — Mon, 05 Dec 2016 07:22:33 GMT

can you share the latest log?

Maybe add a trace on filesize as well