Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I'm looking to create a table (straight or pivot) that contains a grouped column. I think some example data might explain it better. The data I've loaded into QlikView contains like the below:
A | B | C | D | E |
---|---|---|---|---|
Text | More Text | Further Text | Type 1 | Text |
Text | More Text | Further Text | Type 2 | Text |
aaa | bbb | ccc | Type 1 | ddd |
eee | fff | ggg | Type 1 | hhh |
iii | jjj | kkk | Type 2 | lll |
I want the column D to be grouped/pivoted/etc so that the data looks like the below:
A | B | C | D | E |
---|---|---|---|---|
Text | More Text | Further Text | Type 1/Type 2 (or dropdown/twist) | Text |
aaa | bbb | ccc | Type 1 | ddd |
eee | fff | ggg | Type 1 | hhh |
iii | jjj | kkk | Type 2 | lll |
I'm not sure whether to do this in the load of the data, or using the pivot table. I've tried using both, but I can't get the effect I want. I can make all columns pivot, but not just one.
Cheers
you could do that in script using something like this:
LOAD A,
B,
C,
concat(D, '/') AS D,
E
FROM XXXXXXXX
Group by A, B, C, E;
Hope this helps.
Fernando
That doesn't work for me, but I think that's because I've also got a fairly sizeable filter in my load script:
filters(
Remove(Row, RowCnd(Compound,
RowCnd(CellValue, 6, StrCnd(null)),
RowCnd(CellValue, 8, StrCnd(null))
)),
Replace(7, top, StrCnd(null)),
Replace(6, top, StrCnd(null)),
Replace(5, top, StrCnd(null)),
Replace(4, top, StrCnd(null)),
Replace(3, top, StrCnd(null)),
Replace(2, top, StrCnd(null)),
Replace(9, top, StrCnd(null)),
Replace(10, top, StrCnd(null)),
Replace(8, bottom, StrCnd(null))
))
Column 8 is the one I want to concantanate, but it concats everything BUT column 8 when I use your method.