topic Re: creating a template in QlikView

creating a template

Tue, 17 Sep 2013 08:54:20 GMT

Hi all,

I have a database from which i am trying to retrieve data and create a template. the template has the following structure.

ID	description	value
1	number of people in brazil	1200
1.1	number of women in brazil	800
1.2	number of men in brazil	400
2	number of cars in brazil	09807

to create this template I have written the following piece of code.

tblDetails:

load *inline [

ID, desciption

1, number of people

1.1, number of women in brazil

1.2, number if men in brazil

2, number of cars in brazil

];

tblTemplate:

load

'1' as ID,

[date if entry],

count(people) as value

resident tblMaster

where country ='BRAZIL'

group by [date of entry]

;

concatenate load

'1.1' as ID,

[date of entry],

count(people) as value

where tblMaster

where country='BRAZIL' and gender='female'

group by [date of entry]

;

ETC.

this works fine until a certain ID has zero records because of its conditions (this cause the row to disappear from the straight table). My requirement is to display the row (as the template needs to be of a fixed format) and insert the value column as zero.

Does any one know how i can do this?

Cheers,

Re: creating a template

Gysbert_Wassenaar — Tue, 17 Sep 2013 09:35:28 GMT

Add the end of the script you can load a set of default records for the missing id's

concatenate load * inline [

ID, description, value

1, number of people in brazil, 0

1.1, number of women in brazil, 0

1.2, number of men in brazil, 0

2, number of cars in brazil, 0

3, number of something in brazil, 0

3.1, number of something else in brazil, 0

....etc

]

where not exists(ID);

This way only the records of the missing ID's will get loaded from the inline load.

Re: creating a template

Tue, 17 Sep 2013 10:44:50 GMT

hi,

thanks for the help. i can see the logic behind it however it doesnt seem to work and i am not sure why.

I have amended the original message. maybe the additional code i included helps explain why it doesnt work?

cheers