topic Re: some coding assistance please in QlikView

some coding assistance please

QFanatic — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: some coding assistance please

edwin — Tue, 29 Sep 2020 18:50:18 GMT

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 ;

Re: some coding assistance please

Brett_Bleess — Wed, 14 Oct 2020 17:49:41 GMT

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/1470637

Regards,
Brett

Re: some coding assistance please

QFanatic — Thu, 15 Oct 2020 07:04:37 GMT

Thank you very much Edwin