topic Re: "If then else" equivalent inside load? in QlikView

"If then else" equivalent inside load?

Tue, 14 Apr 2015 17:26:40 GMT

Hi,

I'm loading log files and trying to parse them into something reasonable for qlikview.

Am I going at it in a silly way, should I just quit trying and do conversion with something else before loading into qv or can you think of something to make the script reasonable?

I don't want to go through writing all the conditions (might be more than 50) just to realize that it would take ages to run it for a millions of log lines. Is there a way to include the field name inside If statement so I could first try the most common ones like this: "If something then this As id Else if....". Or are my performance concerns unnecessary?

log: LOAD @1 As line FROM log.txt (txt, codepage is 1252, no labels, delimiter is \x7, msq); //load the log

temp1:

LOAD line,

If(left(line,8)='login ID',mid(line,10,10) As id,

if(left(line,8)='Username',mid(line,10,10) As user,

if(left(line,14)='End of session', 'eos') As eos,

....lots of similar comparisons with things that I want to pull info from to different fields....

RESIDENT temp1;

drop table log;

temp2:

LOAD

If(isnull(id), peek(id), id) As id, //peek to copy the id from the previous line

If(isnull(user), peek(user), user) As id, //peek to copy the id from the previous line

......

RESIDENT temp2;

drop table temp1;

Re: "If then else" equivalent inside load?

Tue, 14 Apr 2015 17:31:13 GMT

The syntax for the IF statement does allow for nesting

IF(MyExpressionIsTrue, THEN DO THIS, ELSE DO THIS)

IF(MyExpressionIsTrue, THEN DO THIS,

IF(MyExpressionsIsSomethingElseInstead, THEN DO THIS,

IF(MyExpressionsIsAThirdThing, THEN DO THIS, ELSE DO THIS IF IT Didn't Match anything)))

Re: "If then else" equivalent inside load?

Tue, 14 Apr 2015 17:40:12 GMT

Inside LOAD I can only get a value to put in a field:

If(1=1, 'blabla') As this,

But I would like the field name to be inside the condition too so I I could do:

If(x=1, 'blabla' As this,

if(x=2, '2blabla' As that,

if(x=3.....

edit: field name changing this,that... etc.

Re: "If then else" equivalent inside load?

rwunderlich — Tue, 14 Apr 2015 17:52:42 GMT

You can't conditionally create the field, you can only conditionally create the field value. My usual approach is to go through and create all the fields (even the nulls) and then break the table into normalized pieces to create the one to many relationships. See this example:

Qlikview Cookbook: Load Input File Containing Multiple Record Types http://qlikviewcookbook.com/recipes/download-info/load-input-file-containing-multiple-record-types/

Another approach I use is to take one pas through the data to assign ids (for inter-record association) and "tag" the records with a type. Then process the resident table with multiple LOAD where type="".

-Rob

Re: "If then else" equivalent inside load?

ramoncova06 — Tue, 14 Apr 2015 17:55:22 GMT

you could do it but not on the way you want, QV if's are similar to SQL cases where each case has an specific name,

though you can try by doing something like this

If(x=1, 'blabla' As this

if(x<>1 and x=2, '2blabla' As that,

etc ...

Re: "If then else" equivalent inside load?

Anonymous — Tue, 14 Apr 2015 17:57:20 GMT

There is something that can help:

IF A=B THEN

some script, e.g.
LOAD
...
blabla as this,
...
FROM
...

ELSE

some other script, e.g.
LOAD
...
blabla as that,
...
FROM
...

ENDIF

Re: "If then else" equivalent inside load?

Tue, 14 Apr 2015 18:23:47 GMT

Another approach I use is to take one pas through the data to assign ids (for inter-record association) and "tag" the records with a type. Then process the resident table with multiple LOAD where type="".

-Rob

I thought about this too but I came to the conclusion that it doesn't change that much. I'd still have to go through all the individual if statements to "tag" the line.

Guess I'll just try and see if there will be any performance issues.

Re: "If then else" equivalent inside load?

rwunderlich — Tue, 14 Apr 2015 22:10:40 GMT

The tagging approach can simplify things a bit. You can do the tagging with a map instead of if(). And then each load statement deals only with the fields it knows will be on that record type, eliminating additional if().

-Rob