Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

comparing resident tables

Hi,

I have two resident tables. I want to fetch records from first table(with field sdp_msisdn) which does not contain msisdns in 2nd table(msisdn).How can I achieve this and display the records in a bar chart?

mea_SDP_exists_RPX_NotExists_10_9:

LOAD MER_ID as SDP_MER_ID_10_9,

     MER_FROM_DTTM as SDP_MER_FROM_DTTM_10_9,

     MER_TO_DTTM as MER_TO_DTTM_10_9,

    SDP_MSISDN,

     SDP_MSISDN_10_9,

     SDP_ACCOUNT_ID as SDP_ACCOUNT_ID_10_9,

     SDP_MIN_SERVICE_CLASS as SDP_MIN_SERVICE_CLASS_10_9,

     SDP_MIN_NAME as SDP_MIN_NAME_10_9,

     SDP_MIN_BALANCE as SDP_MIN_BALANCE_10_9,

     PSP_STATUS as PSP_STATUS_10_9,

     PSP_INIT_ACT_DT as PSP_INIT_ACT_DT_10_9,

    // PPD_STATUS as PPD_STATUS_10_9,

    if(PPD_STATUS='','D',if(PPD_STATUS='-','D',PPD_STATUS)) as PPD_STATUS_10_9,

     PPD_STS_CHANGED_TIME as PPD_STS_CHANGED_TIME_10_9

FROM

[\\prdfsdpo02\vdifolders$\SMujumd3\Desktop\331662_10-9.txt]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

In_RPX_Next_Day:

LOAD PAS_ID as PAS_ID_TG,

     STS_ID,

     SFL_ID,

     RUN_ID,

     SUB_ID,

     ACTIVATION_DATE,

     IMEI,

     IMSI,

     IN_ID,

     STATUS,

     STATUS_CHANGED_TIME,

     MSISDN,

     RATE_PLAN_ID,

     PROMO_CODE,

     DEC_RATE,

     BAL_EXPIRY_DATE,

     TEST_NUM,

     LAST_UPDATE,

     SARAS_UPDATE_TIME,

     UPDATE_FILE_NAME,

     LAST_REFILL,

     FOCAL_DATE,

     DEACT_ID,

     EXP_FLAG,

     ZERO_BAL_FLAG,

     SIM,

     TOTAL_REFILL,

     PORT_TYPE,

     CDR_LAST_UPDATE,

     REG_STATUS,

     LAST_REG_STATUS_UPDATE,

     TOTAL_CALL_MIN,

     TOTAL_SMS,

     TOTAL_CHARGE,

     BAL_AFTER,

     FIRST_CALL,

     COUNT_CALLS,

     REACT_ID,

     ESCHEATED_COUPON,

     REACT_DAYS_ID,

     BIT_REACT_REFILL,

     BIT_ADDS,

     BIT_DAILY_ADD,

     BIT_DISC,

     BIT_ADJ,

     BIT_DEACT,

     BIT_REACT,

     BIT_ENDSUB,

     MSISDN_1

FROM

[\\prdfsdpo02\vdifolders$\SMujumd3\Desktop\Timing GAp_Next Day in RPX.txt]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Tags (1)
8 Replies
MVP
MVP

Re: comparing resident tables

Hi

If you can change the load order, first load the second table, and then load the first table with this where clause:

     Where Not(Exists(MSISDN, SDP_MSISDN))

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: comparing resident tables

Try this:

In_RPX_Next_Day:

LOAD PAS_ID as PAS_ID_TG,

     STS_ID,

     SFL_ID,

     RUN_ID,

     SUB_ID,

     ACTIVATION_DATE,

     IMEI,

     IMSI,

     IN_ID,

     STATUS,

     STATUS_CHANGED_TIME,

     MSISDN,

     RATE_PLAN_ID,

     PROMO_CODE,

     DEC_RATE,

     BAL_EXPIRY_DATE,

     TEST_NUM,

     LAST_UPDATE,

     SARAS_UPDATE_TIME,

     UPDATE_FILE_NAME,

     LAST_REFILL,

     FOCAL_DATE,

     DEACT_ID,

     EXP_FLAG,

     ZERO_BAL_FLAG,

     SIM,

     TOTAL_REFILL,

     PORT_TYPE,

     CDR_LAST_UPDATE,

     REG_STATUS,

     LAST_REG_STATUS_UPDATE,

     TOTAL_CALL_MIN,

     TOTAL_SMS,

     TOTAL_CHARGE,

     BAL_AFTER,

     FIRST_CALL,

     COUNT_CALLS,

     REACT_ID,

     ESCHEATED_COUPON,

     REACT_DAYS_ID,

     BIT_REACT_REFILL,

     BIT_ADDS,

     BIT_DAILY_ADD,

     BIT_DISC,

     BIT_ADJ,

     BIT_DEACT,

     BIT_REACT,

     BIT_ENDSUB,

     MSISDN_1

FROM

[\\prdfsdpo02\vdifolders$\SMujumd3\Desktop\Timing GAp_Next Day in RPX.txt]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

mea_SDP_exists_RPX_NotExists_10_9:

LOAD MER_ID as SDP_MER_ID_10_9,

     MER_FROM_DTTM as SDP_MER_FROM_DTTM_10_9,

     MER_TO_DTTM as MER_TO_DTTM_10_9,

    SDP_MSISDN,

     SDP_MSISDN_10_9,

     SDP_ACCOUNT_ID as SDP_ACCOUNT_ID_10_9,

     SDP_MIN_SERVICE_CLASS as SDP_MIN_SERVICE_CLASS_10_9,

     SDP_MIN_NAME as SDP_MIN_NAME_10_9,

     SDP_MIN_BALANCE as SDP_MIN_BALANCE_10_9,

     PSP_STATUS as PSP_STATUS_10_9,

     PSP_INIT_ACT_DT as PSP_INIT_ACT_DT_10_9,

    // PPD_STATUS as PPD_STATUS_10_9,

    if(PPD_STATUS='','D',if(PPD_STATUS='-','D',PPD_STATUS)) as PPD_STATUS_10_9,

     PPD_STS_CHANGED_TIME as PPD_STS_CHANGED_TIME_10_9

FROM

[\\prdfsdpo02\vdifolders$\SMujumd3\Desktop\331662_10-9.txt]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq) Where Not Exists( SDP_MSISDN,MSISDN);

Not applicable

Re: comparing resident tables

Hi,

But I want to keep the load order the same for a reason.

Is it possible to load such records with a different name in a different resident table

like load records from FIRST TABLE WHICH DOES NOT HAVE COMMON RECORDS IN SECOND TABLE INTO THIRD TABLE?

MVP
MVP

Re: comparing resident tables

If you don't load the data into qlikview first, it would not know which records to be excluded in the following load. Right? You can try to load the single field from the source to reduce the overhead. The field has to be there loaded in qv from qvd or resident load whichever would be easier for you.

Not applicable

Re: comparing resident tables

But, can I do the same with joins?

Say

first table-

(first table inner join second table on msisdn) => this wud give me the required answer?

or can I put a condition in the chart saying load the fields from msisdn only if it is not in second table?

I am looking for alternate ways because I need the entire data in the first table to be displayed in another chart.

But, thank you for your time

MVP
MVP

Re: comparing resident tables

Inner join and in-chart options are possible. Give a try.

Not applicable

Re: comparing resident tables

I gave this...

But it says field<MSISDN> not found.

In_RPX_Next_Day:

LOAD

     MSISDN

FROM

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

mea_SDP_exists_RPX_NotExists_10_9:

LOAD

     SDP_MSISDN

    

FROM

(txt, codepage is 1252, embedded labels, delimiter is ';', msq)

Where Not Exists( SDP_MSISDN,MSISDN);

MVP
MVP

Re: comparing resident tables

try :

Not Exists( SDP_MSISDN,MSISDN)

Not Exists( MSISDN,SDP_MSISDN)

Community Browser