topic Re: How to flag column values in QlikView

How to flag column values

Fri, 12 Sep 2014 13:18:57 GMT

Hi Friends,

I have a data set like this

A B C Code Amount

1 2 3 PC 900

1 3 4 PM 800

1 2 4 TM 800

1 2 6 PC 800

1 3 4 PM 800

Now I have decide the Amount column as sales amount, Insurance amount and Commisions Amount based on the combinations of A B C and PC.

For example if A =1 B=2 C=3 and Code =PC then the Amount should be flagged as Sales amount.

if A=1 B=3 C=4 and Code=PM then the Amount should be flageed as Insurance amount.

Re: How to flag column values

MK_QSL — Fri, 12 Sep 2014 13:26:18 GMT

Load

If(A=1 and B=2 and C=3 and Code = 'PC', 'SalesAmount',

If(A=1 and B=3 and C=4 and Code = 'PM', 'InsuranceAmount')) as Flag

Inline

[

A, B, C, Code, Amount

1, 2, 3, PC, 900

1, 3, 4, PM, 800

1, 2, 4, TM, 800

1, 2, 6, PC, 800

1, 3, 4, PM, 800

];

Re: How to flag column values

narband2778 — Fri, 12 Sep 2014 13:34:12 GMT

Hi,

if(A = 1 and B = 2 and C = 3 and Code = 'pc', '1', '0') AS [Sales Flag amount]

Re: How to flag column values

Fri, 12 Sep 2014 13:44:37 GMT

Thanks

But I have lot of combinations.

Can I use a key for combinations and flag them????

Re: How to flag column values

Fri, 12 Sep 2014 13:48:15 GMT

Left join the 3 concatenated field A&B&C as Something to a table with combinations you created.

Re: How to flag column values

Fri, 12 Sep 2014 13:49:58 GMT

You can create a only field having all the Flags or create individual Flag,

The best method is, use the Apply Map to create Single flag field.

FLAG_MAP:

LOAD * INLINE [

COND, FLAG

1-2-3-PC, Sales Amount

1-2-4-PM, Insurance Amount

];

LOAD *, ApplyMap('FLAG_MAP', A&'-'&B&'-'&C&'-'&Code , 'Others') AS Amount_Type

Inline

[

A, B, C, Code, Amount

1, 2, 3, PC, 900

1, 3, 4, PM, 800

1, 2, 4, TM, 800

1, 2, 6, PC, 800

1, 3, 4, PM, 800

];

Re: How to flag column values

Fri, 12 Sep 2014 14:10:27 GMT

Hi Thanks

But I am getting blank for Amount Type in the table.

Could you please explain the 'Others' in ApplyMapt fucntion.

Thanks

Pavan

Re: How to flag column values

Fri, 12 Sep 2014 14:11:41 GMT

Hi Thanks

But I am getting blank for Amount Type in the table.

Could you please explain the 'Others' in ApplyMapt fucntion.

Thanks

Pavan

Re: How to flag column values

geert_gelade — Fri, 12 Sep 2014 14:16:07 GMT

The reason you get blanks is because you have to use a "mapping load".

So change

FLAG_MAP:

LOAD * INLINE [

FLAG_MAP:

MAPPING LOAD * INLINE [

The 'Others' will display if no value is found in de mapping table.

Re: How to flag column values

MK_QSL — Fri, 12 Sep 2014 14:30:46 GMT

Just wondering what was wrong with my answer ?

Re: How to flag column values

Fri, 12 Sep 2014 14:34:16 GMT

The answer was correct.

But Apply Map gave me easiest way to solve my problem.

Thanks for your answer