Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
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;
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,
Thank you buddy !
It works perfectly. This peace of code i should note for it. aweful
Best Regards,
Thanks honour I also learned something new... Thanxxx...
Regards,
Nilesh Gangurde