Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

iahmadmca1
New Contributor III

Re: Compare two table field with flag.

Mukarm,

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

Thanks,

Irshad Ahmad

Re: Compare two table field with flag.

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
New Contributor III

Re: Compare two table field with flag.

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.

testmeter.png

mdmukramali
Valued Contributor III

Re: Compare two table field with flag.

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
New Contributor III

Re: Compare two table field with flag.

Hi Mukram,

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

Untitled.png

Thanks,

Irshad Ahmad.

mdmukramali
Valued Contributor III

Re: Compare two table field with flag.

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

iahmadmca1
New Contributor III

Re: Compare two table field with flag.

Thank you Mukram,Its working fine.

Re: Compare two table field with flag.

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
New Contributor III

Re: Compare two table field with flag.

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

Re: Compare two table field with flag.

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.