Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

iahmadmca1
New Contributor III

Compare two table field with flag.

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

1 Solution

Accepted Solutions
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

22 Replies

Re: Compare two table field with flag.

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

Re: Compare two table field with flag.

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.

mdmukramali
Valued Contributor III

Re: Compare two table field with flag.

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.

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

Re: Compare two table field with flag.

Thanks Sunny, I will check and let you know.

iahmadmca1
New Contributor III

Re: Compare two table field with flag.

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

Highlighted
mdmukramali
Valued Contributor III

Re: Compare two table field with flag.

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

Re: Compare two table field with flag.

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