topic Re: nested if statements within Load in QlikView

nested if statements within Load

Thu, 03 Jul 2014 03:08:10 GMT

Hi, I've got 2 fields jaProjectID and jfProjectID and I want combinedProjectID in the 3rd column

The data (simplified) is like so:

jaProjectID jfProjectID combinedProjectID

30 - 30

31 - 31

- P40 P40 <--using Alt results in '-'. Seems like it doesn't like alphanumeric mix.

- - -

My load function:

LOAD

jaProjectID

, jfProjectID

, if(not IsNull(jfProjectID), jfProjectID, if(not isNull(jaProjectID), jaProjectID, '*')) as combinedProjectID

//Alt(jaProject, jfProjectID as combinedProjectID <-- doesn't seem to recognise non-numbers and shows as "-"

- the nested "if" statement looks messy -- so I tried this but doesn't work

LOAD

jaProjectID

, jfProjectID

, If (IsNULL(jaProjectID) and IsNull(jfProjectID)) then

else <....>

Question: is there another way (eg. case statements, switch or something) to replace this mess: if(not IsNull(jfProjectID), jfProjectID, if(not isNull(jaProjectID), jaProjectID, '*'))?

Re: nested if statements within Load

Gysbert_Wassenaar — Thu, 03 Jul 2014 12:00:41 GMT

If one of the two fields jaProjectID and jfProjectID is always null you can simply concatenate the two fields:

LOAD

jaProjectID,

jfProjectID ,

jaProjectID & jfProjectID as combinedProjectID

From ...

Re: nested if statements within Load

Fri, 04 Jul 2014 01:43:45 GMT

Thanks, Gysbert, I appreciate it. That would be really useful in part of my real code .

For this situation, I think it looks like I'm stuck with a messy nested if statement. I was hoping there was something similar to the CASE statement in SQL. Because sometimes they are both null and then I want it to show '*'.

Re: nested if statements within Load

Fri, 04 Jul 2014 01:46:45 GMT

Try Alt function.