Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
skr002244
New Contributor III

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

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

AB
HOST-7344649A8D974E74HOST-0788564003D72AEF
HOST-7344649A8D974E74HOST-A02BF7E1B9ADA36D
HOST-7344649A8D974E74HOST-54AA0D8B5401A1C2
HOST-7344649A8D974E74HOST-DE6F9EC80D4D7C58
HOST-7344649A8D974E74HOST-7453A7E317FCF4AF
HOST-DE6F9EC80D4D7C58HOST-54AA0D8B5401A1C2
HOST-363E102D24CFAB71HOST-A4198073007B0118
HOST-363E102D24CFAB71HOST-2BBED3AC0BB98104
HOST-0788564003D72AEFHOST-C4565C1643219060
HOST-0788564003D72AEFHOST-F5D85B7DCDD8A93C
HOST-0788564003D72AEFHOST-54AA0D8B5401A1C2
HOST-0788564003D72AEFHOST-7453A7E317FCF4AF
HOST-0788564003D72AEFHOST-A02BF7E1B9ADA36D
HOST-0788564003D72AEFHOST-DE6F9EC80D4D7C58
HOST-0788564003D72AEFHOST-76FAA6DC0347DA12
HOST-0788564003D72AEFHOST-E662F28EFFC7D77D
HOST-0788564003D72AEFHOST-6B659DFBAC76F491
HOST-0788564003D72AEFHOST-EA50C80CC9354652
HOST-0788564003D72AEFHOST-7344649A8D974E74
HOST-0788564003D72AEFHOST-9FB14758AEB3D444
HOST-FBE542F270B45636HOST-A4198073007B0118
HOST-FBE542F270B45636HOST-2BBED3AC0BB98104
HOST-7453A7E317FCF4AFHOST-0788564003D72AEF
HOST-7453A7E317FCF4AFHOST-F5D85B7DCDD8A93C
HOST-7453A7E317FCF4AFHOST-A02BF7E1B9ADA36D
HOST-7453A7E317FCF4AFHOST-54AA0D8B5401A1C2
HOST-54AA0D8B5401A1C2HOST-A02BF7E1B9ADA36D
HOST-54AA0D8B5401A1C2HOST-7453A7E317FCF4AF
HOST-EA50C80CC9354652HOST-0788564003D72AEF
HOST-EA50C80CC9354652HOST-A02BF7E1B9ADA36D
HOST-EA50C80CC9354652HOST-54AA0D8B5401A1C2
HOST-EA50C80CC9354652HOST-DE6F9EC80D4D7C58
HOST-EA50C80CC9354652HOST-7453A7E317FCF4AF
HOST-76FAA6DC0347DA12HOST-0788564003D72AEF
HOST-76FAA6DC0347DA12HOST-F5D85B7DCDD8A93C
HOST-76FAA6DC0347DA12HOST-A02BF7E1B9ADA36D
HOST-76FAA6DC0347DA12HOST-DE6F9EC80D4D7C58
HOST-76FAA6DC0347DA12HOST-7453A7E317FCF4AF
HOST-6B659DFBAC76F491HOST-0788564003D72AEF
HOST-6B659DFBAC76F491HOST-F5D85B7DCDD8A93C
HOST-6B659DFBAC76F491HOST-A02BF7E1B9ADA36D
HOST-6B659DFBAC76F491HOST-DE6F9EC80D4D7C58
HOST-6B659DFBAC76F491HOST-7453A7E317FCF4AF
HOST-A02BF7E1B9ADA36DHOST-F5D85B7DCDD8A93C
HOST-A02BF7E1B9ADA36DHOST-54AA0D8B5401A1C2
HOST-A02BF7E1B9ADA36DHOST-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 :

  

GroupAB
Group-4HOST-7344649A8D974E74HOST-0788564003D72AEF
Group-4HOST-7344649A8D974E74HOST-A02BF7E1B9ADA36D
Group-4HOST-7344649A8D974E74HOST-54AA0D8B5401A1C2
Group-4HOST-7344649A8D974E74HOST-DE6F9EC80D4D7C58
Group-4HOST-7344649A8D974E74HOST-7453A7E317FCF4AF
Group-4HOST-DE6F9EC80D4D7C58HOST-54AA0D8B5401A1C2
Group-14HOST-363E102D24CFAB71HOST-A4198073007B0118
Group-14HOST-363E102D24CFAB71HOST-2BBED3AC0BB98104
Group-4HOST-0788564003D72AEFHOST-C4565C1643219060
Group-4HOST-0788564003D72AEFHOST-F5D85B7DCDD8A93C
Group-4HOST-0788564003D72AEFHOST-54AA0D8B5401A1C2
Group-4HOST-0788564003D72AEFHOST-7453A7E317FCF4AF
Group-4HOST-0788564003D72AEFHOST-A02BF7E1B9ADA36D
Group-4HOST-0788564003D72AEFHOST-DE6F9EC80D4D7C58
Group-4HOST-0788564003D72AEFHOST-76FAA6DC0347DA12
Group-4HOST-0788564003D72AEFHOST-E662F28EFFC7D77D
Group-4HOST-0788564003D72AEFHOST-6B659DFBAC76F491
Group-4HOST-0788564003D72AEFHOST-EA50C80CC9354652
Group-4HOST-0788564003D72AEFHOST-7344649A8D974E74
Group-4HOST-0788564003D72AEFHOST-9FB14758AEB3D444
Group-16HOST-FBE542F270B45636HOST-A4198073007B0118
Group-16HOST-FBE542F270B45636HOST-2BBED3AC0BB98104
Group-4HOST-7453A7E317FCF4AFHOST-0788564003D72AEF
Group-4HOST-7453A7E317FCF4AFHOST-F5D85B7DCDD8A93C
Group-4HOST-7453A7E317FCF4AFHOST-A02BF7E1B9ADA36D
Group-4HOST-7453A7E317FCF4AFHOST-54AA0D8B5401A1C2
Group-4HOST-54AA0D8B5401A1C2HOST-A02BF7E1B9ADA36D
Group-4HOST-54AA0D8B5401A1C2HOST-7453A7E317FCF4AF
Group-4HOST-EA50C80CC9354652HOST-0788564003D72AEF
Group-4HOST-EA50C80CC9354652HOST-A02BF7E1B9ADA36D
Group-4HOST-EA50C80CC9354652HOST-54AA0D8B5401A1C2
Group-4HOST-EA50C80CC9354652HOST-DE6F9EC80D4D7C58
Group-4HOST-EA50C80CC9354652HOST-7453A7E317FCF4AF
Group-4HOST-76FAA6DC0347DA12HOST-0788564003D72AEF
Group-4HOST-76FAA6DC0347DA12HOST-F5D85B7DCDD8A93C
Group-4HOST-76FAA6DC0347DA12HOST-A02BF7E1B9ADA36D
Group-4HOST-76FAA6DC0347DA12HOST-DE6F9EC80D4D7C58
Group-4HOST-76FAA6DC0347DA12HOST-7453A7E317FCF4AF
Group-4HOST-6B659DFBAC76F491HOST-0788564003D72AEF
Group-4HOST-6B659DFBAC76F491HOST-F5D85B7DCDD8A93C
Group-4HOST-6B659DFBAC76F491HOST-A02BF7E1B9ADA36D
Group-4HOST-6B659DFBAC76F491HOST-DE6F9EC80D4D7C58
Group-4HOST-6B659DFBAC76F491HOST-7453A7E317FCF4AF
Group-4HOST-A02BF7E1B9ADA36DHOST-F5D85B7DCDD8A93C
Group-4HOST-A02BF7E1B9ADA36DHOST-54AA0D8B5401A1C2
Group-4HOST-A02BF7E1B9ADA36DHOST-7453A7E317FCF4AF