Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Generate Missing Dates

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_iddateclicks
14/1/20188
14/2/2018-
14/3/20181
14/4/20185
14/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

1 Solution

Accepted Solutions
Siva_Sankar
Master II
Master II

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.

View solution in original post

1 Reply
Siva_Sankar
Master II
Master II

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.