Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
So, I have this data and situation as shown below
I am trying to achieve is .. exhibit 3, where I want to
1. remove the rows for AA
2. Try to put the total for AA in columns, where the levels match.
Appreciate your inputs.
I even tried to If(not Match(Type, 'AA'), Type) .. but then it just ignores the those rows and displays data something like as:
Type | Lvl1 | Lvl2 | Total | AA |
FR | FC | 208 | 100 | 0 |
TI | FC | 208 | 101 | 0 |
FF | FC | 208 | 102 | 0 |
FR | BD | 505 | 103 | 0 |
TI | BD | 505 | 104 | 0 |
FF | BD | 505 | 105 | 0 |
FR | CT | 710 | 106 | 0 |
TI | CT | 710 | 107 | 0 |
FF | CT | 710 | 108 | 0 |
It looks to me that your AA row is an aggregation of other rows in your table. Am I correct?
If so you don't need to create them, QlikView can calculate the totals for you.
Remove the AA row and try using the expression Sum(TOTAL <Type> Total)
This is the data I get from the source, I am not aggregating them manually
I see, then you could try this.
Load *
From ...
Where Type <> 'AA'
;
LEFT JOIN
LOAD
Lvl1,
Lvl2,
Total as AA
From ...
Where Type = 'AA'
;
No way to handle that on table/column .. cause I would not have much control on data load ?
I'm sorry, I didn't understand your last comment .
Did you find issues with my last suggestion using join?
i mean I don't have control over data load part, so I am not able to write script for data loading. Instead mandate is to work within app to manipulate columns
I would ask the data manager if the AA is in fact the aggregated sum of all other Types. If the answer is yes then you could use the following expression. Sum({<Type -= {'AA'}>}TOTAL <Type> Total)