topic Re: How to create IF statement to make field from another field. in QlikView

How to create IF statement to make field from another field.

nickjose7 — Wed, 25 Nov 2020 16:16:04 GMT

kush141087‌ its.anandrjs‌ antoniotiman‌ loveisfail serj_shu

Hi,

I have the following data:

SKU Code	Category
001	AA
002	BB
003	CC
004	DD
005	EE
006	FF

Now I want to create a field say "Channel" with values 'X4' and 'X6' like this:

Load

SKU Code,

If(Wildmatch (Category,'AA', 'BB', 'CC','DD'),'X4',

If(Wildmatch (Category,'AA', 'BB', 'CC','DD','EE','FF'),'X6')) as Channel

But this is not working. It is giving correct value of X4 but in case of X6 it is only considering values which are not covered in X4 i.e EE & FF only.

Please help.

Note: I cannot take two loads as it will multiple my data which happens to be SKU wise volume which I have to later show in a straight table where Category and Channel both will be dimensions. Basically the need for channel is to show sub totals on the basis of X4 & X6.

Thanks

Nick

Re: How to create IF statement to make field from another field.

Chanty4u — Wed, 13 Jun 2018 13:47:52 GMT

what is the result you want to see?

Re: How to create IF statement to make field from another field.

Chanty4u — Wed, 13 Jun 2018 13:52:44 GMT

May be try this

load *,

if(match(Code,'AA','BB','CC','DD'),'x4',null()) as new ,

if(match(Code,'AA','BB','CC','DD','EE','FF'),'x6',null()) as new1;

LOAD * Inline [

SKU ,Code

001 ,AA

002 ,BB

003 ,CC

004 ,DD

005 ,EE

006 ,FF

];

exit SCRIPT;

Re: How to create IF statement to make field from another field.

nickjose7 — Wed, 13 Jun 2018 13:55:22 GMT

I want to see something like below:

Let our data be:

SKU	Category	Volume
001	AA	10
002	BB	20
003	CC	30
004	DD	40
005	EE	50
006	FF	60

Then the result should be like:

Channel	Volume
X4	100
X6	210

Re: How to create IF statement to make field from another field.

Kushal_Chawda — Wed, 13 Jun 2018 17:37:02 GMT

try like below

Data:

LOAD * Inline [

SKU ,Category

001 ,AA

002 ,BB

003 ,CC

004 ,DD

005 ,EE

006 ,FF

];

Link:

load distinct Category,

'X4' as Channel

resident Data

where match (Category,'AA', 'BB', 'CC','DD');

Concatenate(Link)

load distinct Category,

'X6' as Channel

resident Data

where match (Category,'AA', 'BB', 'CC','DD','EE','FF');

Re: How to create IF statement to make field from another field.

nickjose7 — Thu, 14 Jun 2018 02:15:28 GMT

Thanks Kushal. This works though I was looking for correction in the original IF statement if possible.