Create New Column by comparing two columns values

[Hariz]

I am new to qliksense and trying to find a way to create new column by comparing two column values(Id1 and Id2) and writing to the parent column

Eg : Input

Game	Id1	Id2
Baseball	11
Baseball US		11
Baseball Germany		11
Baseball England		11
Football	22
Football Brazil		22
Football France		22

Output

Game	Id1	Game1	Game2	Game3
Baseball	11	Baseball US	Baseball Germany	Baseball England
Football	22	Football Brazil	Football France

[stevejoyce]

I don't think it will be that straight forward, but below should work.

I am utilizing generic load.  Have a look at this article if you aren't familiar with it https://community.qlik.com/t5/Qlik-Design-Blog/The-Generic-Load/ba-p/1473470

//First loading in data input
temp_data:
LOAD
Game,
Id1,
Id2
FROM https://community.qlik.com/t5/App-Development/Create-New-Column-by-comparing-two-columns-values/td-p...
//https://community.qlik.com/t5/QlikView-App-Dev/Merging-Year-from-column-into-X-Axis-with-Months/td-p...
(html, utf8, embedded labels, table is @1)
;

//There may need to be an intermediate sorting of temp_data as this assumes data is sorted correctly

//Generating game# that resets when there is a new Id2
game_number:
load
Game,
Id2,
If(peek(Id2)=Id2, peek(game_number) + 1, 1) as game_number //get game # that resets for new game
resident temp_data
where Id2 > 0;

game_number2:
generic load
Id2,
'Game'&game_number as Game_Id,
Game
resident game_number;

drop table game_number;

//join generic load into 1 table

Set vListOfTables = ;

For vTableNo = 0 to NoOfTables()

Let vTableName = TableName($(vTableNo)) ;

If Subfield(vTableName,'.',1)='game_number2' Then

Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

End If

Next vTableNo

game_number3:
Load distinct
Id2
resident temp_data
where Id2 > 0;

For each vTableName in $(vListOfTables)

Left Join (game_number3) Load * Resident [$(vTableName)];

Drop Table [$(vTableName)];

Next vTableName

//join parent Game records with Game# fields created
outer join (game_number3)
load
Game,
Id1 as Id2
resident temp_data
where Id1 > 0;

//drop original temp table
drop table temp_data;

//rename Id field
rename field Id2 to Id1;

[Kushal_Chawda]

@Hariz  another way

// Assuming that your data is sorted. You may need to sort it before to get desire output

T1:
LOAD ID,
           Games,
           SubStringCount(Games,';')+1 as Cnt_string;
LOAD
           Id2 as ID,
          Concat(Game,';',RecNo()) as Games
From Table
Where len(trim(Id2))>0
Group by Id2;

string_cnt:
LOAD max(Cnt_string) as max_string_cnt;
LOAD FieldValue('Cnt_string',RecNo()) as Cnt_string
AutoGenerate FieldValueCount('Cnt_string');

let vMaxCntStrng = Peek('max_string_cnt',0,'string_cnt');

DROP Table string_cnt;

for i=1 to $(vMaxCntStrng)

Formula1:
LOAD 'subfield(Games,'&chr(39)&';'&chr(39)&',$(i)) as Games$(i)' as Formula
AutoGenerate 1;

NEXT

Formula:
NoConcatenate
LOAD Concat(Formula,','&chr(10)) as Formula
Resident Formula1;

DROP Table Formula1;

let vFormula = Peek('Formula',0,'Formula');

DROP Table Formula;

T2:
LOAD ID,
            $(vFormula)
Resident T1;

DROP Table T1;

Left Join(T2)
LOAD
             Id1 as ID,
             Game
From Table
Where len(trim(Id1))>0;