topic Issue in exist clause in QlikView

Issue in exist clause

Sat, 05 Jul 2014 08:17:38 GMT

Hi all,

I want to calculate missing ad from the data for example I am having two table one is comp and other is table2.

If key (unit +cat+client+publishdate) in comp data table and in table 2 same key is not present (unit+cat+client+publishdate2)

with in the range of publishdate+7 or publishdate-7 then it is called fullmissing but if key is present within the same range i.e publishdate+7 or publishdate-7 then it is called partial missing.

I tried with exist function but i stucked on date range

Kindly find the sample data for reference. Any help would be appreciated.

Regards

Sumita Chawla

Re: Issue in exist clause

MK_QSL — Sat, 05 Jul 2014 09:21:50 GMT

T1:

Load

unit,

cat,

client,

space,

Date(Date#(pubdate,'MM/DD/YYYY')) as pubdate,

unit&cat&client&space as Key,

unit&cat&client&space&pubdate as Key2

Inline

[

unit, cat, client, space, pubdate

indo, a1, c1, 300, 30/05/2014

indo, a2, c2, 400, 06/01/2014

indo, a2, c3, 500, 06/01/2014

indo, a2, C4, 400, 06/05/2014

indo, a2, C5, 400, 06/08/2014

];

Outer Join

Load

unit,

cat,

client,

space,

Date(Date#(pubdate,'MM/DD/YYYY')) as pubdate2,

unit&cat&client&space as Key,

unit&cat&client&space&pubdate as Key2

Inline

[

unit, cat,client, space, pubdate

indo, a1, c1, 300, 30/05/2014

indo, a2, c2, 400, 06/01/2014

indo, a3, c7, 400, 06/01/2014

indo, a2, C5, 400, 06/03/2014

]Where Exists (Key,unit&cat&client&space);

Final:

Load

If(Len(Trim(pubdate2))=0, 'Full Missing',If(pubdate2 <= (pubdate+7) or pubdate2 >= (pubdate-7),'partial missing')) as Flag

Resident T1;

Drop Table T1;

Re: Re: Issue in exist clause

Sat, 05 Jul 2014 12:22:30 GMT

Hello Manish,

Thanks for your reply.

I tried with your solution but I found that output is coming different means Partial missing is not coming exact.

And space I can't take in key.

Can u plz check the output for partial missing?