Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

fill the missed fields during scriptload

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;

5 Replies
nilesh_gangurde
Valued Contributor

fill the missed fields during scriptload

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

MVP
MVP

fill the missed fields during scriptload

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

fill the missed fields during scriptload

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

fill the missed fields during scriptload

Thank you buddy !

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

Best Regards,

nilesh_gangurde
Valued Contributor

fill the missed fields during scriptload

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

Regards,

Nilesh Gangurde