Help with Qlik Sense Script.

[Qlikuser225]

Hello All,

Can anyone please help me how to achive below result using QLik sense script.

My data:

ID, Class, Count

123, AAA, 23

123,BBB,34

234, CCC, 29

345,AAA,34

456, AAA, 23

456,BBB,34

456, CCC, 29

My requirement is i need to have every ID all three classes and if there us no count listed then it should be 0 like belwo.(HIGHLIGHTED IN BOLD)

ID, Class, Count

123, AAA, 23

123,BBB,34

123,CCC,0

234,AAA,0

234,BBB,0

234, CCC, 29

345,AAA,34

345,BBB,0

345,CCC,0

456, AAA, 23

456,BBB,34

456, CCC, 29

Thanks in Advance

[zhaofeng · Accepted solution]

Try this

T1:
Load * Inline [
ID,Class,Count
123,AAA,23
123,BBB,34
234,CCC,29
345,AAA,34
456,AAA,23
456,BBB,34
456,CCC,29
]
;

NoConcatenate


T2:
Load Distinct
    ID
Resident T1;

join(T2)


Load Distinct
    Class
Resident T1;

left join(T2)
Load
    ID, 
    Class, 
    Count
Resident T1;


NoConcatenate

T3:
Load
    ID, 
    Class,
    if(isnull(Count),0,Count) as Count
Resident T2;



Drop Tables T1,T2;

[Qlikuser225]

Thansk for your reply, 

I am not getting expected results when i have more fields in the table like

My data:

ID, ID2,YearQ,Class, Count

123, 555,2022Q1, AAA, 23

123,555,2022Q1,BBB,34

123, 555,2022Q2, AAA, 25

123,555,2022Q2,BBB,36

234,666,2022Q1, CCC, 29

234,666,2022Q2, CCC, 33

456,888,2022Q1, AAA, 23

456,888,2022Q1,BBB,34

456,888,2022Q1,CCC, 29

456,888,2022Q2, AAA, 23

456,888,2022Q2,BBB,34

456,888,2022Q2,CCC, 29

Expected results

ID, ID2,YearQ,Class, Count

123, 555,2022Q1, AAA, 23

123,555,2022Q1,BBB,34

123,555,2022Q1,CCC,0

123, 555,2022Q2, AAA, 25

123,555,2022Q2,BBB,36

123,555,2022Q2,CCC,0

234,666,2022Q1, AAA, 0

234,666,2022Q1, BBB, 0

234,666,2022Q1, CCC, 29

234,666,2022Q2, AAA, 0

234,666,2022Q2, BBB, 0

234,666,2022Q2, CCC, 33

456,888,2022Q1, AAA, 23

456,888,2022Q1,BBB,34

456,888,2022Q1,CCC, 29

456,888,2022Q2, AAA, 23

456,888,2022Q2,BBB,34

456,888,2022Q2,CCC, 29

Thanks Again