Duplicates based multi field flagging and data transformation through script . Help needed .

[shekhar_analyti]

Hi All ,

I need to achieve below transformation through script . Not sure how to get output . 

Input 

Output

FLAG1 is based on KEY1 ; if there are multiple entries of a value in KEY1 then for any 1 row of its should be flagged as 1 and rest others as 0  ( as we seen in FLAG1 field) .

Similarly FLAG2 is based on KEY2 AND FLAG3 is based on KEY3

Flag_Input:

LOAD * INLINE [

KEY1, KEY2, KEY3, DECADE, QUANTITY, ZONE
MICROSOFT-US-WINDOWS, MICROSOFT-US, MICROSOFT, 1970, 2000, E
MICROSOFT-US-WINDOWS, MICROSOFT-US, MICROSOFT, 1980, 300, E
MICROSOFT-US-WINDOWS, MICROSOFT-US, MICROSOFT, 1990, 4000, W
MICROSOFT-US-WINDOWS, MICROSOFT-US, MICROSOFT, 2000, 6000, N
MICROSOFT-US-SECURITY, MICROSOFT-US, MICROSOFT, 1970, 1000, E
MICROSOFT-US-SECURITY, MICROSOFT-US, MICROSOFT, 1980, 1500, W
MICROSOFT-US-SECURITY, MICROSOFT-US, MICROSOFT, 1990, 4000, N
MICROSOFT-US-SECURITY, MICROSOFT-US, MICROSOFT, 2000, 3000, S
GOOLE-US-SECURITY, GOOGLE-US, GOOGLE, 2000, 1000, N
MACAFEE-US-SECURITY, MACAFEE-US, MACAFEE, 2000, 500, E
MACAFEE-US-SECURITY, MACAFEE-US, MACAFEE, 1970, 0, N
MACAFEE-US-SECURITY, MACAFEE-US, MACAFEE, 1980, 0, N
MACAFEE-US-SECURITY, MACAFEE-US, MACAFEE, 1990, 20, S
MACAFEE-US-FIREWALL, MACAFEE-US, MACAFEE, 2000, 100, E
MACAFEE-US-FIREWALL, MACAFEE-US, MACAFEE, 1970, 0, S
MACAFEE-US-FIREWALL, MACAFEE-US, MACAFEE, 1980, 0, N
MACAFEE-US-FIREWALL, MACAFEE-US, MACAFEE, 1990, 0, E
];

Thank you .

[shekhar_analyti]

Adding qlik scripting giants

@sunny_talwar  
 @marcus_sommer 

[sunny_talwar · Accepted solution]

Try this

Flag_Input:
LOAD * INLINE [
    KEY1, KEY2, KEY3, DECADE, QUANTITY, ZONE
    MICROSOFT-US-WINDOWS, MICROSOFT-US, MICROSOFT, 1970, 2000, E
    MICROSOFT-US-WINDOWS, MICROSOFT-US, MICROSOFT, 1980, 300, E
    MICROSOFT-US-WINDOWS, MICROSOFT-US, MICROSOFT, 1990, 4000, W
    MICROSOFT-US-WINDOWS, MICROSOFT-US, MICROSOFT, 2000, 6000, N
    MICROSOFT-US-SECURITY, MICROSOFT-US, MICROSOFT, 1970, 1000, E
    MICROSOFT-US-SECURITY, MICROSOFT-US, MICROSOFT, 1980, 1500, W
    MICROSOFT-US-SECURITY, MICROSOFT-US, MICROSOFT, 1990, 4000, N
    MICROSOFT-US-SECURITY, MICROSOFT-US, MICROSOFT, 2000, 3000, S
    GOOLE-US-SECURITY, GOOGLE-US, GOOGLE, 2000, 1000, N
    MACAFEE-US-SECURITY, MACAFEE-US, MACAFEE, 2000, 500, E
    MACAFEE-US-SECURITY, MACAFEE-US, MACAFEE, 1970, 0, N
    MACAFEE-US-SECURITY, MACAFEE-US, MACAFEE, 1980, 0, N
    MACAFEE-US-SECURITY, MACAFEE-US, MACAFEE, 1990, 20, S
    MACAFEE-US-FIREWALL, MACAFEE-US, MACAFEE, 2000, 100, E
    MACAFEE-US-FIREWALL, MACAFEE-US, MACAFEE, 1970, 0, S
    MACAFEE-US-FIREWALL, MACAFEE-US, MACAFEE, 1980, 0, N
    MACAFEE-US-FIREWALL, MACAFEE-US, MACAFEE, 1990, 0, E
];

FinalTable:
LOAD *,
	 If(KEY1 = Previous(KEY1), 0, 1) as FLAG1,
	 If(KEY2 = Previous(KEY2), 0, 1) as FLAG2,
	 If(KEY3 = Previous(KEY3), 0, 1) as FLAG3
Resident Flag_Input
Order By KEY1, KEY2, KEY3;

DROP Table Flag_Input;