topic Possible with IF? in QlikView

Possible with IF?

Wed, 15 Jul 2015 14:01:00 GMT

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.

Re: Possible with IF?

sinanozdemir — Wed, 15 Jul 2015 14:16:42 GMT

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;

Re: Possible with IF?

antoniotiman — Wed, 15 Jul 2015 14:52:33 GMT

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

Re: Possible with IF?

Wed, 15 Jul 2015 14:54:47 GMT

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?

Thu, 16 Jul 2015 12:36:59 GMT

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?

Thu, 16 Jul 2015 12:52:56 GMT

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

Re: Possible with IF?

sinanozdemir — Thu, 16 Jul 2015 13:17:11 GMT

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?

Thu, 16 Jul 2015 13:30:13 GMT

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?

settu_periasamy — Thu, 16 Jul 2015 15:19:56 GMT

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.

Re: Possible with IF?

sinanozdemir — Thu, 16 Jul 2015 15:30:30 GMT

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?

Mon, 20 Jul 2015 08:30:06 GMT

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