some coding assistance please

[QFanatic]

hi guys

PFA

The data in the model are table volumes, per day and hour.

The problem we sometimes get, is that the whole day is missing and we need to know about those days.

So I...

1) Read the data and create a key existing of the REQ_date and TABLE_NAME.

2). Then I generate a normal calendar using the REQ_DATE and later on, incorporating the TABLE_NAME as well.

My intent is to :

1) Find out which combination of REQ_DATE and TABLE_NAME have no entries in the data and then to Insert/Append these empty rows, with a dummy REQ_HOUR of '00'. So that it at least shows up in the Chart on the first sheet.

I've got most of that right, except the last part - the insert part..I need to take the initially loaded transaction data and just append the rows that contain nothing - please can you assist me with the code - I think my code is very rudimentary - it can probably do with much better looking and performing code.

Much appreciated.

[edwin · Accepted solution]

if i understand the requirement correctly, you wish to add rows in your table t1 for missing dates with the measure=0

instead of creating a separate table MissingValues, use those dates to concatenate to the t1 table.  the following code continues yours (add to your script before end script line)

NoConcatenate
req_hr:
load Distinct
REQ_HOUR
Resident
t1
;

join (Missing_Values)
LOAD
REQ_HOUR
Resident
req_hr
;  //code up to here just adds REQ_HOUR to your missing dates
//each missing date will have 24 rows from HR=0 to 23
drop table req_hr;

Concatenate (t1)  //now add the dates and HRS to table t1 with 0 volume
load
%Key,
0 as HOUR_VOLUME,
date#(dat1,'YYYYMMDD') as REQ_DATE,
dat1 as REQ_DATE2,
REQ_HOUR,
tabl as TABLE_NAME,
weekday(date#(dat1,'YYYYMMDD')) as WKDAY,
'HOURLY' as TYPE_GRAPH
Resident
Missing_Values;

drop table Missing_Values  //you dont need this anymore
;

[Brett_Bleess]

The following Design Blog post may be of some help too, but I am not sure.  If the above post did work, be sure to close the thread by using the Accept as Solution button on the post...

https://community.qlik.com/t5/Qlik-Design-Blog/How-to-populate-a-sparsely-populated-field/ba-p/14706...

Regards,
Brett

[QFanatic]

Thank you very much Edwin