Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
wjtam206
Contributor
Contributor

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.

View solution in original post