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

[nickjose7]

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

[Chanty4u]

what is the result you want to see?

[Chanty4u]

May be try this

a:

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;

[nickjose7]

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

[Kushal_Chawda · Accepted solution]

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');

[nickjose7]

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