Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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?