topic fill the missed fields during scriptload in QlikView

fill the missed fields during scriptload

Sun, 13 May 2012 12:01:40 GMT

Hi together,

i have a urgent problem and did not how i can handle it. Daily, I get a origin excel source document to load in qlikview. unfortunately, did

this excel document not contain all datarows that i need. In this case the following datarows are not listed ( look at my green highlited datarows )

How can i fill the missed datarows during the scriptload ?

Excelsource:

and this is the target table that i want to build in qlikview:

I am very grateful for a practical advice. An example would be nice. Thank you !

Best Regards

fill the missed fields during scriptload

nilesh_gangurde — Sun, 13 May 2012 12:16:27 GMT

Hi Honour,

Make the inline table of the three rows and then concate that table with the your excelsource table.

The script will be as follows:

Load * from [Excelsource];

concatenate

Load * inline [

Product,country,quartal,year,value

alpha,france,1,2011,0

bravo,germany,2,2011,0

bravo,germany,4,2011,0

]

Hope this helps.

Regards,

Nilesh Gangurde

fill the missed fields during scriptload

swuehl — Sun, 13 May 2012 13:35:40 GMT

If you don't know upfront which lines are missing, you could create a table with all possible combinations for Product/Country, Year and Quartal. Then join this table to your read in data and fill in the missing zeros.

Maybe like this (I create some dummy data first, since I can't easily copy your sample data from the bitmap image):

//Create some sample data

INPUT:

LOAD

chr(65+floor(RAND()*4)) as Product,

chr(65+floor(RAND()*4)) as Country,

ceil(RAND()*4) as Quarter,

2011+floor(RAND()*2) as Year,

ceil(RAND()*100) as Value

autogenerate 25;

//Create a table with all possible combinations

PRODUCTCOUNTRY:

LOAD distinct Product, Country resident INPUT;

join (PRODUCTCOUNTRY) load Distinct Year resident INPUT;

join (PRODUCTCOUNTRY) load Distinct Quarter Resident INPUT;

//join the created table with the original data

left join (PRODUCTCOUNTRY) load * Resident INPUT;

drop table INPUT;

// fill in missing zeros

RESULT:

noconcatenate LOAD Product, Country, Year, Quarter, if(len(Value),Value,0) as Value Resident PRODUCTCOUNTRY order by Product, Country, Year, Quarter;

drop table PRODUCTCOUNTRY;

fill the missed fields during scriptload

Sun, 13 May 2012 20:56:59 GMT

Hi Nilesh,

thank you for your advice. I think your example code is not scalability enough for my case.

With thousands of lines that would be very much working

Look the solution of swuehl. That's what I call an smart solution.

Best Regards,

fill the missed fields during scriptload

Sun, 13 May 2012 20:59:24 GMT

Thank you buddy !

It works perfectly. This peace of code i should note for it. aweful

Best Regards,

fill the missed fields during scriptload

nilesh_gangurde — Mon, 14 May 2012 05:38:18 GMT

Thanks honour I also learned something new... Thanxxx...

Regards,

Nilesh Gangurde