topic Create New Column by comparing two columns values in App Development

Create New Column by comparing two columns values

Hariz — Fri, 27 Aug 2021 20:50:12 GMT

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

Re: Create New Column by comparing two columns values

stevejoyce — Fri, 27 Aug 2021 21:35:32 GMT

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/1831898
//https://community.qlik.com/t5/QlikView-App-Dev/Merging-Year-from-column-into-X-Axis-with-Months/td-p/1831139
(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;

Re: Create New Column by comparing two columns values

Kushal_Chawda — Fri, 27 Aug 2021 23:33:12 GMT

@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;

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;