topic Re: Compare two table field with flag. in QlikView

Compare two table field with flag.

iahmadmca1 — Tue, 26 Dec 2017 11:45:40 GMT

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

Re: Compare two table field with flag.

sunny_talwar — Tue, 26 Dec 2017 12:11:33 GMT

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?

Re: Compare two table field with flag.

iahmadmca1 — Tue, 26 Dec 2017 12:20:44 GMT

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.

Re: Compare two table field with flag.

sunny_talwar — Tue, 26 Dec 2017 12:24:11 GMT

May be use ApplyMap

Don't join - use Applymap instead

Re: Compare two table field with flag.

mdmukramali — Tue, 26 Dec 2017 12:24:23 GMT

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.

Re: Compare two table field with flag.

sunny_talwar — Tue, 26 Dec 2017 12:28:12 GMT

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

];

Re: Compare two table field with flag.

iahmadmca1 — Tue, 26 Dec 2017 12:30:04 GMT

Thanks Sunny, I will check and let you know.

Re: Compare two table field with flag.

iahmadmca1 — Wed, 27 Dec 2017 08:18:42 GMT

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

Re: Compare two table field with flag.

mdmukramali — Wed, 27 Dec 2017 08:37:06 GMT

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.

Re: Compare two table field with flag.

iahmadmca1 — Wed, 27 Dec 2017 08:46:44 GMT

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

Re: Compare two table field with flag.

iahmadmca1 — Wed, 27 Dec 2017 09:23:05 GMT

Mukarm,

I have attached the script.Please refer it and suggest me with solution.

Thanks,

Irshad Ahmad

Re: Compare two table field with flag.

sunny_talwar — Wed, 27 Dec 2017 09:57:57 GMT

Which field are you joining on? Meter Number? But it is named differently in both the tables, it won't join on Meter Number... may be that is what the issue is... and I don't understand your flag... If Meter Number = MeterNumber? This is different then the sample data you provided... what is the goal here?

Re: Compare two table field with flag.

iahmadmca1 — Wed, 27 Dec 2017 10:13:46 GMT

The main goal is here, I need to check how many meters are matching and how many meters are not matching between two qvd, that I need to find out.that is why I am using flag here.

Provided the below sample screenshot for your reference.

Re: Compare two table field with flag.

mdmukramali — Wed, 27 Dec 2017 10:29:02 GMT

Hi,

Can you try the below Script

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 Number],'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.

Re: Compare two table field with flag.

iahmadmca1 — Wed, 27 Dec 2017 11:10:07 GMT

Hi Mukram,

When I am using the above code it is throwing the below error.Please find the below screenshots for your reference.

Thanks,

Irshad Ahmad.

Re: Compare two table field with flag.

mdmukramali — Wed, 27 Dec 2017 11:14:27 GMT

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.

Re: Compare two table field with flag.

iahmadmca1 — Wed, 27 Dec 2017 11:28:10 GMT

Thank you Mukram,Its working fine.

Re: Compare two table field with flag.

sunny_talwar — Wed, 27 Dec 2017 12:05:43 GMT

Please refrain yourself from marking your own responses as correct or helpful... Please mark mdmukramali‌'s response as correct answer and any other helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: Compare two table field with flag.

iahmadmca1 — Wed, 27 Dec 2017 12:17:35 GMT

Okay Sunny, Moving forward I will do as you say.

Re: Compare two table field with flag.

sunny_talwar — Wed, 27 Dec 2017 12:19:59 GMT

Why not do it this time also? The reason I ask this is because this solution might help another person looking for a similar issue. If we you do not mark the appropriate answer as correct and helpful, the other users might find it difficult to look for the solution.