How to create a grouping column based on two columns and certain looping criteria ?

skr002244 — Fri, 09 Mar 2018 06:23:34 GMT

I want to create a grouping column based on two columns and certain criteria. The scenario: Below is the given table with column A & B

A	B
HOST-7344649A8D974E74	HOST-0788564003D72AEF
HOST-7344649A8D974E74	HOST-A02BF7E1B9ADA36D
HOST-7344649A8D974E74	HOST-54AA0D8B5401A1C2
HOST-7344649A8D974E74	HOST-DE6F9EC80D4D7C58
HOST-7344649A8D974E74	HOST-7453A7E317FCF4AF
HOST-DE6F9EC80D4D7C58	HOST-54AA0D8B5401A1C2
HOST-363E102D24CFAB71	HOST-A4198073007B0118
HOST-363E102D24CFAB71	HOST-2BBED3AC0BB98104
HOST-0788564003D72AEF	HOST-C4565C1643219060
HOST-0788564003D72AEF	HOST-F5D85B7DCDD8A93C
HOST-0788564003D72AEF	HOST-54AA0D8B5401A1C2
HOST-0788564003D72AEF	HOST-7453A7E317FCF4AF
HOST-0788564003D72AEF	HOST-A02BF7E1B9ADA36D
HOST-0788564003D72AEF	HOST-DE6F9EC80D4D7C58
HOST-0788564003D72AEF	HOST-76FAA6DC0347DA12
HOST-0788564003D72AEF	HOST-E662F28EFFC7D77D
HOST-0788564003D72AEF	HOST-6B659DFBAC76F491
HOST-0788564003D72AEF	HOST-EA50C80CC9354652
HOST-0788564003D72AEF	HOST-7344649A8D974E74
HOST-0788564003D72AEF	HOST-9FB14758AEB3D444
HOST-FBE542F270B45636	HOST-A4198073007B0118
HOST-FBE542F270B45636	HOST-2BBED3AC0BB98104
HOST-7453A7E317FCF4AF	HOST-0788564003D72AEF
HOST-7453A7E317FCF4AF	HOST-F5D85B7DCDD8A93C
HOST-7453A7E317FCF4AF	HOST-A02BF7E1B9ADA36D
HOST-7453A7E317FCF4AF	HOST-54AA0D8B5401A1C2
HOST-54AA0D8B5401A1C2	HOST-A02BF7E1B9ADA36D
HOST-54AA0D8B5401A1C2	HOST-7453A7E317FCF4AF
HOST-EA50C80CC9354652	HOST-0788564003D72AEF
HOST-EA50C80CC9354652	HOST-A02BF7E1B9ADA36D
HOST-EA50C80CC9354652	HOST-54AA0D8B5401A1C2
HOST-EA50C80CC9354652	HOST-DE6F9EC80D4D7C58
HOST-EA50C80CC9354652	HOST-7453A7E317FCF4AF
HOST-76FAA6DC0347DA12	HOST-0788564003D72AEF
HOST-76FAA6DC0347DA12	HOST-F5D85B7DCDD8A93C
HOST-76FAA6DC0347DA12	HOST-A02BF7E1B9ADA36D
HOST-76FAA6DC0347DA12	HOST-DE6F9EC80D4D7C58
HOST-76FAA6DC0347DA12	HOST-7453A7E317FCF4AF
HOST-6B659DFBAC76F491	HOST-0788564003D72AEF
HOST-6B659DFBAC76F491	HOST-F5D85B7DCDD8A93C
HOST-6B659DFBAC76F491	HOST-A02BF7E1B9ADA36D
HOST-6B659DFBAC76F491	HOST-DE6F9EC80D4D7C58
HOST-6B659DFBAC76F491	HOST-7453A7E317FCF4AF
HOST-A02BF7E1B9ADA36D	HOST-F5D85B7DCDD8A93C
HOST-A02BF7E1B9ADA36D	HOST-54AA0D8B5401A1C2
HOST-A02BF7E1B9ADA36D	HOST-7453A7E317FCF4AF

First the first row value of column A has to be considered.

1.Lets say HOST-7344649A8D974E74 then we have to give all the corresponding values of A in column B a particular group name.The corresponding values of HOST-7344649A8D974E74 are HOST-0788564003D72AEF, HOST-A02BF7E1B9ADA36D,HOST-54AA0D8B5401A1C2,HOST-DE6F9EC80D4D7C58,HOST-7453A7E317FCF4AF these values will be given a particular group value.Lets say Group-4.

2. Now the first corresponding value in column B of Column A : HOST-0788564003D72AEF will be considered as the value of column A and all the corresponding values of HOST-0788564003D72AEF in column be will be named as Group-4. This loop will continue for all the values present in column B for the Column A value of HOST-7344649A8D974E74.

3.The above 2 steps will continue for each unique value in column A and its corresponding values.

4.There shouldn't be any duplicates created.

The result :

Group	A	B
Group-4	HOST-7344649A8D974E74	HOST-0788564003D72AEF
Group-4	HOST-7344649A8D974E74	HOST-A02BF7E1B9ADA36D
Group-4	HOST-7344649A8D974E74	HOST-54AA0D8B5401A1C2
Group-4	HOST-7344649A8D974E74	HOST-DE6F9EC80D4D7C58
Group-4	HOST-7344649A8D974E74	HOST-7453A7E317FCF4AF
Group-4	HOST-DE6F9EC80D4D7C58	HOST-54AA0D8B5401A1C2
Group-14	HOST-363E102D24CFAB71	HOST-A4198073007B0118
Group-14	HOST-363E102D24CFAB71	HOST-2BBED3AC0BB98104
Group-4	HOST-0788564003D72AEF	HOST-C4565C1643219060
Group-4	HOST-0788564003D72AEF	HOST-F5D85B7DCDD8A93C
Group-4	HOST-0788564003D72AEF	HOST-54AA0D8B5401A1C2
Group-4	HOST-0788564003D72AEF	HOST-7453A7E317FCF4AF
Group-4	HOST-0788564003D72AEF	HOST-A02BF7E1B9ADA36D
Group-4	HOST-0788564003D72AEF	HOST-DE6F9EC80D4D7C58
Group-4	HOST-0788564003D72AEF	HOST-76FAA6DC0347DA12
Group-4	HOST-0788564003D72AEF	HOST-E662F28EFFC7D77D
Group-4	HOST-0788564003D72AEF	HOST-6B659DFBAC76F491
Group-4	HOST-0788564003D72AEF	HOST-EA50C80CC9354652
Group-4	HOST-0788564003D72AEF	HOST-7344649A8D974E74
Group-4	HOST-0788564003D72AEF	HOST-9FB14758AEB3D444
Group-16	HOST-FBE542F270B45636	HOST-A4198073007B0118
Group-16	HOST-FBE542F270B45636	HOST-2BBED3AC0BB98104
Group-4	HOST-7453A7E317FCF4AF	HOST-0788564003D72AEF
Group-4	HOST-7453A7E317FCF4AF	HOST-F5D85B7DCDD8A93C
Group-4	HOST-7453A7E317FCF4AF	HOST-A02BF7E1B9ADA36D
Group-4	HOST-7453A7E317FCF4AF	HOST-54AA0D8B5401A1C2
Group-4	HOST-54AA0D8B5401A1C2	HOST-A02BF7E1B9ADA36D
Group-4	HOST-54AA0D8B5401A1C2	HOST-7453A7E317FCF4AF
Group-4	HOST-EA50C80CC9354652	HOST-0788564003D72AEF
Group-4	HOST-EA50C80CC9354652	HOST-A02BF7E1B9ADA36D
Group-4	HOST-EA50C80CC9354652	HOST-54AA0D8B5401A1C2
Group-4	HOST-EA50C80CC9354652	HOST-DE6F9EC80D4D7C58
Group-4	HOST-EA50C80CC9354652	HOST-7453A7E317FCF4AF
Group-4	HOST-76FAA6DC0347DA12	HOST-0788564003D72AEF
Group-4	HOST-76FAA6DC0347DA12	HOST-F5D85B7DCDD8A93C
Group-4	HOST-76FAA6DC0347DA12	HOST-A02BF7E1B9ADA36D
Group-4	HOST-76FAA6DC0347DA12	HOST-DE6F9EC80D4D7C58
Group-4	HOST-76FAA6DC0347DA12	HOST-7453A7E317FCF4AF
Group-4	HOST-6B659DFBAC76F491	HOST-0788564003D72AEF
Group-4	HOST-6B659DFBAC76F491	HOST-F5D85B7DCDD8A93C
Group-4	HOST-6B659DFBAC76F491	HOST-A02BF7E1B9ADA36D
Group-4	HOST-6B659DFBAC76F491	HOST-DE6F9EC80D4D7C58
Group-4	HOST-6B659DFBAC76F491	HOST-7453A7E317FCF4AF
Group-4	HOST-A02BF7E1B9ADA36D	HOST-F5D85B7DCDD8A93C
Group-4	HOST-A02BF7E1B9ADA36D	HOST-54AA0D8B5401A1C2
Group-4	HOST-A02BF7E1B9ADA36D	HOST-7453A7E317FCF4AF

topic How to create a grouping column based on two columns and certain looping criteria ? in QlikView

How to create a grouping column based on two columns and certain looping criteria ?