Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
This should be so easy, just can't see it today.
I need to turn a long table into a more sensible one.
Have | Want | ||||||||
Event | Grade | Attendees | Event | Anum | Bnum | Cnum | Dnum | ||
123 | A | 345 | 123 | 345 | 2 | 8 | 77 | ||
123 | B | 2 | 453 | 6 | 0 | 0 | 52 | ||
123 | C | 8 | 889 | 46 | 0 | 0 | 0 | ||
123 | D | 77 | 4567 | 0 | 0 | 0 | 83 | ||
453 | A | 6 | 98067 | 0 | 56 | 22 | 34 | ||
453 | D | 52 | |||||||
889 | A | 46 | |||||||
4567 | D | 83 | |||||||
98067 | B | 56 | |||||||
98067 | C | 22 | |||||||
98067 | D | 34 |
I've been trying left joins, concatenates, Null as Value, etc
But I keep getting Nulls and can't seem to turn them into 0s. Can someone please put me out of my misery?
I made this work by following what I had with a NoConcatenate reload and using the len(trim trick.
But now I wonder how well this is going to run with 10's of thousands of records?
Any advice?
Just realised I should have said that it's proabaly not true Nulls I'm looking for but Missing data
I made this work by following what I had with a NoConcatenate reload and using the len(trim trick.
But now I wonder how well this is going to run with 10's of thousands of records?
Any advice?
Hi Christine
Try this
Att:
load * Inline [
Event, Grade, Attendees
123, A, 345
123, B, 2
123, C, 8
123, D, 77
453, A, 6
453, D, 52
889, A, 46
4567, D, 83
98067, B, 56
98067, C, 22
98067, D, 34 ];
NoConcatenate
Table1:
load Event,
Sum(Attendees) as Anum
Resident Att
Where Grade = 'A'
group by Event,Grade;
left join
load Event,
Sum(Attendees) as Bnum
Resident Att
Where Grade = 'B'
group by Event,Grade;
left join
load Event,
Sum(Attendees) as Cnum
Resident Att
Where Grade = 'C'
group by Event,Grade;
left join
load Event,
Sum(Attendees) as Dnum
Resident Att
Where Grade = 'D'
group by Event,Grade;
NoConcatenate
Final:
load Event,
if(IsNull(Anum),0,Anum) as Anum,
if(IsNull(Bnum),0,Anum) as Bnum,
if(IsNull(Cnum),0,Anum) as Cnum,
if(IsNull(Dnum),0,Anum) as Dnum
Resident Table1;
DROP Table Table1;
DROP Table Att;
You should get this Table
Event | Anum | Bnum | Cnum | Dnum |
123 | 345 | 345 | 345 | 345 |
453 | 6 | 0 | 0 | 6 |
889 | 46 | 0 | 0 | 0 |
Correction missed the final isnull() statement
Att:
load * Inline [
Event, Grade, Attendees
123, A, 345
123, B, 2
123, C, 8
123, D, 77
453, A, 6
453, D, 52
889, A, 46
4567, D, 83
98067, B, 56
98067, C, 22
98067, D, 34 ];
NoConcatenate
Table1:
load Event,
Sum(Attendees) as Anum
Resident Att
Where Grade = 'A'
group by Event,Grade;
left join
load Event,
Sum(Attendees) as Bnum
Resident Att
Where Grade = 'B'
group by Event,Grade;
left join
load Event,
Sum(Attendees) as Cnum
Resident Att
Where Grade = 'C'
group by Event,Grade;
left join
load Event,
Sum(Attendees) as Dnum
Resident Att
Where Grade = 'D'
group by Event,Grade;
NoConcatenate
Final:
load Event,
if(IsNull(Anum),0,Anum) as Anum,
if(IsNull(Bnum),0,Bnum) as Bnum,
if(IsNull(Cnum),0,Cnum) as Cnum,
if(IsNull(Dnum),0,Dnum) as Dnum
Resident Table1;
DROP Table Table1;
DROP Table Att;
May be using Sum() function around your fields
Did you looked at Generic Load
https://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic
OTOH the data is QlikView ready in it's ideal form: completely serialized.
Which allows you to create the expected output using a simple pivot table. Add two dimensions (Event and Grade) and one expression =sum(Attendees). In Presentation, choose 0 as symbol to display for Missing values. Align all expression values to the right.
Now drag the vertically oriented Grade dimension (first becomes a vertical blue line, then pivots into a horizontal blue line) to the top above the expression column. Release... Tada!.
Hi,
try this,
MapANum:
Mapping LOAD Event,
Attendees as ANum
FROM
Cust.xlsx
(ooxml, embedded labels, table is Sheet1)
where Grade = 'A';
MapBNum:
Mapping LOAD Event,
Attendees as BNum
FROM
Cust.xlsx
(ooxml, embedded labels, table is Sheet1)
where Grade = 'B';
MapCNum:
Mapping LOAD Event,
Attendees as CNum
FROM
Cust.xlsx
(ooxml, embedded labels, table is Sheet1)
where Grade = 'C';
MapDNum:
Mapping LOAD Event,
Attendees as DNum
FROM
Cust.xlsx
(ooxml, embedded labels, table is Sheet1)
where Grade = 'D';
Thing:
LOAD *,
ApplyMap('MapANum',Event,0) as ANum,
ApplyMap('MapBNum',Event,0) as BNum,
ApplyMap('MapCNum',Event,0) as CNum,
ApplyMap('MapDNum',Event,0) as DNum;
LOAD Distinct Event
FROM
Cust.xlsx
(ooxml, embedded labels, table is Sheet1);