Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

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

Hi All ,

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

 

 

Input 

Flagging 1.PNG

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

Flagging 2.PNG

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 .

Labels (1)
1 Solution

Accepted Solutions
Highlighted

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

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;

View solution in original post

2 Replies
Highlighted
Specialist
Specialist

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

Adding qlik scripting giants

@sunny_talwar  
 @marcus_sommer 

Highlighted

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

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;

View solution in original post