Possible with IF?

[#1]

Hello again,

I have the following IF, which works perfectly:

Load *,
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:

Load *,

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?

Thanks in advance for any advice.

[sinanozdemir]

Hi David,

I would just handle this in another preceding load statement:

Load *

Where Flag1 = 1 And TheYear = 2015;

Load *,

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

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

Resident Table1;

[antoniotiman]

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

[#4]

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....

[#5]

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!

[#6]

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

[sinanozdemir]

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

[#8]

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):

LOAD
*,
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

[settu_periasamy]

Hi David,

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

like this..

Check:
Load * INLINE [
     TheKey,RowNo,Supplier
    111, 1,Acme
    112, 2,
    113, 3,
    201, 1,Apple
    202, 2,
    203, 3,Apple
    204, 4,
];
Final:
NoConcatenate
Load TheKey,
  RowNo,
  if(Len(Trim(Supplier))=0 or IsNull(Supplier),Peek(Supplier2),Supplier) as Supplier2
  Resident Check
Order By TheKey, RowNo asc;


Drop Table Check;

PFA for reference.

[sinanozdemir]

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