Need to create Flag column

[bharatkishore]

Hi All,

I have two tables:

Equipments_Details_final:

LOAD  Equipment,

      System_Code,

      Technical_Start_Date,

      left(Calendar_Month_Code,4) &'-'&  right (Calendar_Month_Code,2) as Calendar_Month_Code,

      Contract,

      Warranty,

      Installation,

      UnResolved,

      [T&M],

      [Part Group],

      Key_EMS

Resident Equipments_Details;

drop Table Equipments_Details;

Material_Master_final:

   Key_EMS,

     Install_Date,

     Return_Date

FROM

[..\Material_Master_final.qvd]

(qvd);

Now in Equipments_Details_final based on Key_EMS and Calendar_Month_Code and in Material_Master_final based on Key_EMS and Install_Date  where ever the both keys are matching  i need to create a column called flag .

And that flag column has condition that where ever keys are matching value should be 1 else 0.

Can you please tell me how to get it.

Thanks,

Bharat

[Marco]

Hi,

try with this:

Equipments_Details_final:

Noconcatenate

LOAD

      Equipment,

      System_Code,

      Technical_Start_Date,

      left(Calendar_Month_Code,4) &'-'&  right (Calendar_Month_Code,2) as Calendar_Month_Code,

      Contract,

      Warranty,

      Installation,

      UnResolved,

      [T&M],

      [Part Group],

      Key_EMS

Resident Equipments_Details;

drop Table Equipments_Details;

Left Join (Equipments_Details_final)

LOAD

     Key_EMS,

     Install_Date as Calendar_Month_Code,

     Return_Date

FROM

[..\Material_Master_final.qvd] (qvd);

Equipments_Details:

Noconcatenate

LOAD

          *,

          If(Len(Return_Date) > 0, 1, 0) as Flag

Resident Equipments_Details_final;

Drop Table Equipments_Details_final;

[kaushiknsolanki]

Hi,

Try this code.

Equipments_Details_final:

Load *,Key_EMS&'-'&num(Calendar_Month_Code) as Key;

LOAD  Equipment,

      System_Code,

      Technical_Start_Date,

      left(Calendar_Month_Code,4) &'-'&  right (Calendar_Month_Code,2) as Calendar_Month_Code,

      Contract,

      Warranty,

      Installation,

      UnResolved,

      [T&M],

      [Part Group],

      Key_EMS

Resident Equipments_Details;

drop Table Equipments_Details;

Material_Master_final:

Load If(Exists(Key,Key_EMS&'-'&num(Install_Date)),1,0) as Flag,

   Key_EMS,

     Install_Date,

     Return_Date

FROM

[..\Material_Master_final.qvd]

(qvd);

Regards,

Kaushik Solanki

[bharatkishore]

HI Kaushik,

Thanks a lot for your reply. I tried with your code but the problem is where ever the key matches i should get only single record for that month.. But with help of your code i am getting multiple records.

I have tried other method. please let me know if this code is correct or anything is wrong.

Equipments_Details_final:

LOAD  Equipment,

      System_Code,

      Technical_Start_Date,

      date(Date#(Calendar_Month_Code,'YYYYMM'),'YYYY-MM') as Calendar_Month_Code,

      Contract,

      Warranty,

      Installation,

      UnResolved,

      [T&M],

      [Part Group],

      Key_EMS

Resident Equipments_Details;

drop Table Equipments_Details;

join

load Key_EMS,

      Install_Date,

      Removal_Date

FROM

[..\Material_Master_final.qvd]

(qvd);

NoConcatenate

Test:

load  Equipment,

      System_Code,

      Technical_Start_Date,

      Calendar_Month_Code,

      Contract,

      Warranty,

      Installation,

      UnResolved,

      [T&M],

      [Part Group],

      Key_EMS,

if(Calendar_Month_Code <> Install_Date,Install_Date , null()) as Install_Date,

if(Calendar_Month_Code <> Removal_Date,Removal_Date , null()) as Removal_Date,

if(Calendar_Month_Code=Install_Date,'1' , 0) as Install_Flag ,

if(Calendar_Month_Code=Removal_Date,'1' , 0) as Removal_Flag

Resident Equipments_Details_final;

drop table Equipments_Details_final;

Thanks,

Bharat