Generate Missing Dates

[Anonymous]

Hello,

I am trying to generate missing dates in my table as nulls. Generating Missing Data In QlikView I've used this resource and I am still not getting the output that I want.

Here is the desired output:

keyword_id	date	clicks
1	4/1/2018	8
1	4/2/2018	-
1	4/3/2018	1
1	4/4/2018	5
1	4/5/2018	-

Here is an example of my script:

search_phrase:

LOAD * INLINE [

keyword_id

1,

2,

3,

4,

5

];

kw_temp:

LOAD

keyword_id,

date,

clicks

FROM keyword_metrics.qvd

where exists (keyword_id);

MinMaxDate:    

Load

Min(date(date)) as MinDate,

Max(date(date)) as MaxDate

resident kw_temp;   

Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate');

Drop table MinMaxDate;

kw_product:

Load distinct keyword_id

Resident kw_temp;

join

LOAD

Date(recno()+$(vMinDate)) as date Autogenerate vMaxDate-vMinDate;

right join (kw_temp)

LOAD *

Resident kw_product;

kw_final:

NoConcatenate

LOAD

keyword_id

date,

rangesum(clicks) as clicks

Resident kw_temp

order by search_phrase_id,date;

Drop table kw_temp,keyword_include,kw_product;

However, it is returning an error stating that 'kw_product' table is not found in the right join. I am pretty sure the where Exists () statement is causing this issue. Any ideas?

Thanks

[Siva_Sankar · Accepted solution]

Hi Wesley,

Find the attached qvw file with your script. it is using dummy data .

A join will keep the name of the first table you loaded, no matter if it is a left, right, inner or outer join.

So you need to rename the table after joining it.

I dont find keyword_include table in your script and column search_phrase_id.

if the suggestion didnt work, share sample data.