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

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

shekhar_analyti — Thu, 18 Jun 2020 10:24:09 GMT

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 .

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

shekhar_analyti — Thu, 18 Jun 2020 10:53:47 GMT

Adding qlik scripting giants

@sunny_talwar
@marcus_sommer

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

sunny_talwar — Thu, 18 Jun 2020 12:16:26 GMT

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;