Transforming CSV / QVD import

[#1]

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

[Gysbert_Wassenaar]

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.

[#3]

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

[Gysbert_Wassenaar · Accepted solution]

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;

[#5]

Hi Gysbert,

thank you so much. Easier as thought, and the answer clarified much faster as expected.

Great help,

TTHX,

Mathias