Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Possible with IF?

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.

10 Replies
sinanozdemir
Valued Contributor III

Re: Possible with IF?

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
Honored Contributor III

Re: Possible with IF?

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

Not applicable

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

Not applicable

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!

Not applicable

Re: Possible with IF?

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

sinanozdemir
Valued Contributor III

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

Not applicable

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

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

Re: Possible with IF?

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;

71.JPG

PFA for reference.

sinanozdemir
Valued Contributor III

Re: Possible with IF?

Hi David,

Here is the solution:

Capture.PNG

After that your data model will look like:

Capture2.PNG

Here is the front end table:

Capture3.PNG

I am also attaching the file.

Thanks

Community Browser