topic Re: Need to create Flag column in QlikView

Need to create Flag column

bharatkishore — Wed, 05 Sep 2018 08:11:11 GMT

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

Re: Need to create Flag column

Marco — Wed, 05 Sep 2018 08:59:15 GMT

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;

Re: Need to create Flag column

kaushiknsolanki — Wed, 05 Sep 2018 10:06:49 GMT

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

Re: Need to create Flag column

bharatkishore — Wed, 05 Sep 2018 11:13:23 GMT

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