Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Transforming CSV / QVD import

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
PartnerItems_Total
Items_Successfull
01.01.2013DEMO108
01.01.2013DEMO210070
02.01.2013DEMO2015

Now I would like to have the data in Qlikview in the following way:

DatePartnerItemsStatus
01.01.2013DEMO8successfull
01.01.2013DEMO2unsuccessfull
01.01.2013DEMO270successfull
01.01.2013DEMO230unsuccessfull
02.01.2013DEMO15successfull
02.01.2013DEMO5unsuccessfull

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

1 Solution

Accepted Solutions

Re: Transforming CSV / QVD import

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;


talk is cheap, supply exceeds demand
4 Replies

Re: Transforming CSV / QVD import

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.


talk is cheap, supply exceeds demand
Not applicable

Re: Transforming CSV / QVD import

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

Re: Transforming CSV / QVD import

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;


talk is cheap, supply exceeds demand
Not applicable

Re: Transforming CSV / QVD import

Hi Gysbert,

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

Great help,

TTHX,

Mathias

Community Browser