Issue in exist clause

[#1]

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

[MK_QSL]

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;

[#3]

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?