Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
];
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
];
Hi,
if(A = 1 and B = 2 and C = 3 and Code = 'pc', '1', '0') AS [Sales Flag amount]
Thanks
But I have lot of combinations.
Can I use a key for combinations and flag them????
Left join the 3 concatenated field A&B&C as Something to a table with combinations you created.
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
];
Hi Thanks
But I am getting blank for Amount Type in the table.
Could you please explain the 'Others' in ApplyMapt fucntion.
Thanks
Pavan
Hi Thanks
But I am getting blank for Amount Type in the table.
Could you please explain the 'Others' in ApplyMapt fucntion.
Thanks
Pavan
The reason you get blanks is because you have to use a "mapping load".
So change
FLAG_MAP:
LOAD * INLINE [
to
FLAG_MAP:
MAPPING LOAD * INLINE [
The 'Others' will display if no value is found in de mapping table.
Just wondering what was wrong with my answer ?