fill the missed fields during scriptload

[#1]

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

[nilesh_gangurde]

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

[swuehl · Accepted solution]

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;

[#4]

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,

[#5]

Thank you buddy !

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

Best Regards,

[nilesh_gangurde]

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

Regards,

Nilesh Gangurde