Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlikview Community,
I am strugling with the following challenge and don't get it solved.
I have a CSV / QVD file in the following format:
Date | Partner | Items_Total | Items_Successfull |
---|---|---|---|
01.01.2013 | DEMO | 10 | 8 |
01.01.2013 | DEMO2 | 100 | 70 |
02.01.2013 | DEMO | 20 | 15 |
Now I would like to have the data in Qlikview in the following way:
Date | Partner | Items | Status |
---|---|---|---|
01.01.2013 | DEMO | 8 | successfull |
01.01.2013 | DEMO | 2 | unsuccessfull |
01.01.2013 | DEMO2 | 70 | successfull |
01.01.2013 | DEMO2 | 30 | unsuccessfull |
02.01.2013 | DEMO | 15 | successfull |
02.01.2013 | DEMO | 5 | unsuccessfull |
Meaning: The number of successfull items will be used as it is, and marked with "successfull" in "Status". The number of "Status" "unsuccessfull" items needs to be calculated (Items_Total - Items_Successfull) and putted into the line.
Any idea how to transform my data source to achieve this?
Thanks already,
Mathias
Change the second load to
Load
Date, Partner, Items_Total - Items_Successfull as Items, 'unsuccessfull' as Status
from myqvd.qvd (qvd)
where Items_Total > Items_Successfull;
Like this:
ResultTable:
Load
Date, Partner, Items_Successfull as Items, 'successfull' as Status
from myqvd.qvd (qvd);
Load
Date, Partner, Items_Total - Items_Successfull as Items, 'unsuccessfull' as Status
from myqvd.qvd (qvd);
Because both loads return the same number of fields with the same names they are concatenated into one table.
Hi Gysbert, man - this looks easy. I understand the script.
Is there also any chance to avoid the second LOAD per line if the calculation of (Items_Total - Items_Successfull) will be 0.
Thanks,
Mathias
Change the second load to
Load
Date, Partner, Items_Total - Items_Successfull as Items, 'unsuccessfull' as Status
from myqvd.qvd (qvd)
where Items_Total > Items_Successfull;
Hi Gysbert,
thank you so much. Easier as thought, and the answer clarified much faster as expected.
Great help,
TTHX,
Mathias