Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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