Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

fill the missed fields during scriptload

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:

source.PNG

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

target.PNG

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

Best Regards

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

5 Replies
nilesh_gangurde
Partner - Specialist
Partner - Specialist

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
MVP
MVP

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;

Not applicable
Author

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,


Not applicable
Author

Thank you buddy !

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

Best Regards,

nilesh_gangurde
Partner - Specialist
Partner - Specialist

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

Regards,

Nilesh Gangurde