10 Replies Latest reply: Jul 20, 2015 4:30 AM by David Gilligan

Possible with IF?

Hello again,

I have the following IF, which works perfectly:

If(Flag1) = 'S', tempSpend_NOK * -1, tempSpend_NOK) As ActualSpend_NOK,
If(Flag1 = 'S', tempTax_NOK * -1, tempTax_NOK) As ActualTax_NOK,
If(Flag1 = 'S', tempNetSpend_NOK * -1, tempNetSpend_NOK) As ActualNetSpend_NOK

Resident Table1;

Basically Flag1 determines if the signs should be inverted or not (*-1)....takes the gross, taxesm subtracts for net Norwegian Crowns

Bit I want to take things further, if possible.  I want to use a different field value based on different criteria.  Here is a non-working example:

If(Flag1) = 1, TheField, TheField [WHERE Flag1 = 1) As WhatIsFlag1,

If(Flag2 = 1, TheField2, TheField2 * AnotherField [WHERE TheYear = 2015) As InverIfNeeded

Resident Table1;

Some of these might get quite involved so I wonder if I am even on the right track for this?  Is there a way to handle it in an IF...CASE....do it in SQL instead?

• Re: Possible with IF?

Hi David,

I would just handle this in another preceding load statement:

Where Flag1 = 1 And TheYear = 2015;

If(Flag1) = 1, TheField, TheField) As WhatIsFlag1,

If(Flag2 = 1, TheField2, TheField2 * AnotherField) As InverIfNeeded

Resident Table1;

• Re: Possible with IF?

If(Flag1 = 'S',-1,1)*tempSpend_NOK As ActualSpend_NOK

• Re: Possible with IF?

Let me try and get some bettwe examples to display before I take up your time..  There are a few that I would like to do with IF if possible....but I know there are work arounds....

• Re: Possible with IF?

Hello again...OK...let me try with another example....a real one that I was hoping to do with an IF....or in the LOAD....(sorry for the bad examples before)...

Let's say I have 3 fields, a key (TheKey), A Supplier ID (Supplier) and Line Number (LineNo)....

What I would like to do is, if the Supplier is = to "" (blank but not null), then I want to take the Supplier from LineNo = 1 WHERE for the same key.  One key = 1 line on the invoice, so there can be 10 lines, 9 blank (or maybe less but always there will be one on line number 1)

I tried as an IF and in the LOAD but always come up with a syntax error that doesn't really reflect the problem (it will appear later in the script and have nothing to do with anything I did).

Thanks again!

• Re: Possible with IF?

OH....and also, I would need to create this as a new field, say Supplier2...

• Re: Possible with IF?

Hi David,

It might be better if you post a sample dataset which can reflect the complexity. Sometimes it is difficult to determine the code without seeing the dataset.

Thanks

• Re: Possible with IF?

I can do that....in the mean time, mayhbe this will help.  What I really want to learn how to do, if I can do it, is make in QlikView, basically an SQL Subselect statement.  So, very simply (the dataset is hundreds of thousands of rows):

*,
TheKey
Supplier,

RowNo,,
if(RowNo = 1, Supplier, (SELECT Supplier WHERE RowNo=1) AS Supplier2,   //Sort of how I would do it in SQL
;

So, for example:

TheKey     Row No.     Supplier

111              1                    Acme

112              2

113          3

...

201          1               Apple

202          2

203          3               Apple

204          4

Would become:

So, for example:

TheKey     Row No.     Supplier

111              1                    Acme

112              2                    Acme

113          3               Acme

...

201          1               Apple

202          2               Apple

203          3               Apple

204          4               Apple

• Re: Possible with IF?

Hi David,

Here is the solution:

After that your data model will look like:

Here is the front end table:

I am also attaching the file.

Thanks

• Re: Possible with IF?

many thanks....do you know why I cannot mark a response as an answer...I am logged in but do not see the "mark as answer" any more....would like to give credit where credit is due...

• Re: Possible with IF?

Hi David,

you can use the Peek() function to get the previous value.

like this..

```Check:
TheKey,RowNo,Supplier
111, 1,Acme
112, 2,
113, 3,
201, 1,Apple
202, 2,
203, 3,Apple
204, 4,
];
Final:
NoConcatenate