Solved: Re: Compare two table field with flag. - Qlik Community

iahmadmca1 · ‎2017-12-26

Team,

I am using the below code logic to compare the two fields.It is working with small data but whenever using the huge value of data it is getting stuck.could you please suggest me an alternate solution for this.

Tab1:

LOAD * INLINE [

A, Key

1000, 1

1001, 2

1002, 3

1003, 4

1004, 5

1005, 6

];

join

LOAD * INLINE [

B, Key

1000, 1

1010, 2

1020, 3

1003, 4

1040, 5

1005, 6

];

FinTable:

LOAD *,

if(A=B, 'Yes', 'No') as OWM_Flag

Resident Tab1;

DROP Table Tab1;

Thanks,

Irshad Ahmad

mdmukramali · ‎2017-12-27

Hi Irshad,

Try script.

it was a mistake.

MeterNumber_Map:

Mapping Distinct

MeterNumber as [Meter Number],

'Yes' as Flag

FROM[..\View2.qvd](qvd);

Billing_Metrics:

LOAD METER_EQUIP_MFR_CD & repeat('0', (9 - len(METER_EQUIP_CD))) & METER_EQUIP_CD as [Meter Number],

ApplyMap('MeterNumber_Map',

METER_EQUIP_MFR_CD & repeat('0', (9 - len(METER_EQUIP_CD))) & METER_EQUIP_CD

,'No') as OWM_Flag,

BILL_ACCOUNT_NBR&'-'&PREMISE_NBR&'-'&SERVICE_POINT_NBR as BM_Key,

repeat('0', (10 - len(BILL_ACCOUNT_NBR))) & BILL_ACCOUNT_NBR as [Account Number],

PREMISE_NBR as [Premise Number],

SERVICE_POINT_NBR as [Service Point Number],

SERVICE_POINT_TYPE_NM as [Service Type],

TARIFF_RATE_NM as Tariff,

REVENUE_CLASS_NM as [Revenue Class],

READING_SOURCE_NM as [Reading Source],

READING_SOURCE_IND as [Reading Source Ind],

METER_POINT_TYPE_NM as [Meter Point Type],

READING_DEVICE_NM as [Meter Device],

CONSUMPTION_SOURCE_NM as [Consumption Source],

acct_manager as [Account Manager],

CUSTOMER_FULL_NM as [Customer Name],

Address,

Date(left(METER_POINT_SET_DT,10)) as [Install Date],

//BILLING_YEARMO as [Billing Year Month],

Date(Date#(left(BILLING_YEARMO,6),'YYYYMM'),'YYYY-MM') as [Billing Year Month],

// Date(Date#(left(BILLING_YEARMO,6),'YYYYMM'),'MM-YYYY') as Months,

METER_BILL_GROUP_NBR as [Bill Group],

LAST_UPDATE_DT_TM,

METER_CONDITION_NM,

if(METER_CONDITION_NM='MDM FORCED ESTIMATE',METER_CONDITION_NM,READING_SOURCE_NM) as Reading_Source,

// MONTH_KEY as [Month Key],

if(REVENUE_CLASS_NM='RESIDENTIAL','Residential',

if(REVENUE_CLASS_NM='HOME BUSINESS' or REVENUE_CLASS_NM='LARGE COMM INDUST' or REVENUE_CLASS_NM='SMALL COMM INDUST','Commercial',

if(REVENUE_CLASS_NM='STREET AND HIGHWAY LIGHT' or REVENUE_CLASS_NM='ELECTRIC RAILROADS','Street and Highway'))) as [Rate Type],

if(READING_SOURCE_IND='G','Final',

if(READING_SOURCE_IND='M' or READING_SOURCE_IND='3','Forced',

if(READING_SOURCE_IND='4' or READING_SOURCE_IND='5' or READING_SOURCE_IND='E','Estimated'))) as [Estimation Type]

/*Performing transformation logic based upon the requirement*/

FROM

[..\Estimated_Bill_Metrics.qvd]

(qvd);

LOAD IssueID,

MeterNumber as [Meter Number],

premiseNumber,

[Issue Status],

[ISSUE Category],

BreakDate,

ProjectMeter,

FixDate,

LastAction,

cmoDate,

FieldOrderComplete,

RevokecmoDate,

DeferredCMOorMI,

miDate,

CancelMIDate,

MICompleteDate,

CheckSealDate,

CancelCheckSealDate,

CSCompleteDate,

RevPro,

CancelRevPro,

RPCompleteDate,

PUC,

UnquarantinedDate,

RadioRestartDate,

NonFieldOrderAction,

NonFOActionDate,

closedBy,

assignedTo,

IssueCreateDate

FROM

[..\View2.qvd]

(qvd);

Thanks,

Mukram.

View solution in original post

sunny_talwar · ‎2017-12-26

Do you one to one join between the two tables in your original scenario? In the above table, you are joining on "Key" field... which doesn't repeat in either of the table... is it possible that it repeats in one/both tables in your original example?

iahmadmca1 · ‎2017-12-26

Thanks Sunny for the quick response, yes sunny I am joining one to one the above code is working with small data like 1000 line or 10000 line but while using the 1000000 data it is not working.could you please give me the alternate example on it.

sunny_talwar · ‎2017-12-26

May be use ApplyMap

Don't join - use Applymap instead

mdmukramali · ‎2017-12-26

Hi,

Maybe sunny will suggest something.

If it's One to One Then i think you can try ApplyMap Concept which is much faster in reloading and performance.

sunny_talwar · ‎2017-12-26

Something along these lines

MappingTable:

Mapping

LOAD Key, B INLINE [

B, Key

1000, 1

1010, 2

1020, 3

1003, 4

1040, 5

1005, 6

];

Tab1:

LOAD *,

ApplyMap('MappingTable', Key) as B,

If(A = ApplyMap('MappingTable', Key), 'Yes', 'No') as OWM_Flag;

LOAD * INLINE [

A, Key

1000, 1

1001, 2

1002, 3

1003, 4

1004, 5

1005, 6

];

iahmadmca1 · ‎2017-12-26

Thanks Sunny, I will check and let you know.

iahmadmca1 · ‎2017-12-27

Hi Sunny,

I got confused while applying your actual logic in my QVW.Now I am going to share the actual code which i am using in my qvw. based on meter number I need to check the common meter number and find out the flag.please refer the below code and correct me.

Billing_Metrics:

LOAD

MeterNumber,

If(IsNull(LastAction),'!!No Action!!',LastAction) as [Last Action]

FROM

(qvd);

Join

LOAD METER_EQUIP_MFR_CD & repeat('0', (9 - len(METER_EQUIP_CD))) & METER_EQUIP_CD as [Meter Number],

BILL_ACCOUNT_NBR&'-'&PREMISE_NBR&'-'&SERVICE_POINT_NBR as BM_Key,

repeat('0', (10 - len(BILL_ACCOUNT_NBR))) & BILL_ACCOUNT_NBR as [Account Number]

FROM

(qvd);

Final_Billing_Metrics:

load *,

if([Meter Number]=MeterNumber,'Yes','No') as OWM_Flag

Resident Billing_Metrics;

DROP table Billing_Metrics;

Thanks,

Irshad Ahmad

mdmukramali · ‎2017-12-27

Hi Irshad,

it seems you are trying to join Estimated_Bill_Metrics with Billing_Metrics

but what is the key between this two table to Join?

Because in the first table you have MeterNumber and in the second table you have [Meter Number] so both are not same so it will not make any join based in this fields.

if you are using any other fields then share the script with us.

Thanks,

Mukram.

iahmadmca1 · ‎2017-12-27

Thanks, Mukram,

I can share the script but I need to compare only the meter number.and based on meter number need to set the flag.

Thanks.

Irshad Ahmad