Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello and thank you for reading,
What I need to do is add a new dimension under an existing column. The project I am working on requires that I sum up some values and then that I present them inside the same table, but under a different dimension:
Product | #prod |
---|---|
A | 10 |
B15 | 15 |
C10 | 10 |
D15 | 20 |
F50 | 20 |
The table above would be something like the result table, the "new" dimension has to be add under the column "Product", showing the sum of the C10 and D15 products, as "others", and the drop the listed fields. The end result would look like this:
Product | #prod |
---|---|
A | 10 |
B15 | 15 |
F50 | 20 |
OTHERS | 30 |
This tables also have the month on them, so my idea is to make an aggregation summing the two different products and them group them by the month, to proceed to add them inside under product column, but that last part I dont know how to do it.
Thank you.,
Hello Jesus,
Here is the below method will work for you:
Let’s create Mapping table for the two output values.
NewMap:
MAPPING LOAD
C10, ‘others’ ,
D15,'others';
Next we connect fields to the mapping tables with MAP USING statements. The
statement identifies the field and Mapping table that should be used.
MAP Product USING NewMap;
The mapping will be applied when the field value is created. So how do we
apply the map to an already existing table? We make a copy of the table.
That will cause the latest mapping to be applied
Table2:
NoConcatenate // This is important! We want a new table!
LOAD * RESIDENT Table1;
DROP TABLE Table1; // Drop the original table
This should work.Let me know if this works.
Regards,
Mayank
On Thu, Oct 1, 2015 at 11:57 PM, Sunny Talwar <qcwebmaster@qlikview.com>
just to make it more efficient... You can add the Distinct after LOAD:
Left Join(TableName)
LOAD DISTINCT Product,
If(Match(Product, 'A', 'B15', 'F50'), Product, 'OTHERS') as [New Product]
Resident TableName;