2 Replies Latest reply: Jul 5, 2014 8:22 AM by Sumita Chawla RSS

    Issue in exist clause

    Sumita Chawla

      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
          Manish Kachhia

          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;