Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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);

8 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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
tresesco
MVP
MVP

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
Author

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?

tresesco
MVP
MVP

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
Author

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

tresesco
MVP
MVP

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

Not applicable
Author

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);

tresesco
MVP
MVP

try :

Not Exists( SDP_MSISDN,MSISDN)

Not Exists( MSISDN,SDP_MSISDN)