Compare two table field with flag.

[iahmadmca1]

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

[iahmadmca1]

Mukarm,

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

Thanks,

Irshad Ahmad

[sunny_talwar]

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?

[iahmadmca1]

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.

[mdmukramali]

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.

[iahmadmca1]

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.

[mdmukramali · Accepted solution]

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.

[iahmadmca1]

Thank you Mukram,Its working fine.

[sunny_talwar]

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

[iahmadmca1]

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

[sunny_talwar]

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.