topic comparing resident tables in QlikView

comparing resident tables

Wed, 11 Sep 2013 13:01:12 GMT

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

Re: comparing resident tables

jonathandienst — Wed, 11 Sep 2013 13:07:50 GMT

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

Re: comparing resident tables

tresB — Wed, 11 Sep 2013 13:09:39 GMT

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

Re: comparing resident tables

Wed, 11 Sep 2013 13:14:01 GMT

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?

Re: comparing resident tables

tresB — Wed, 11 Sep 2013 13:34:15 GMT

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.

Re: comparing resident tables

Thu, 12 Sep 2013 03:40:11 GMT

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

Re: comparing resident tables

tresB — Thu, 12 Sep 2013 04:02:06 GMT

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

Re: comparing resident tables

Thu, 12 Sep 2013 05:02:15 GMT

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

Re: comparing resident tables

tresB — Thu, 12 Sep 2013 07:03:31 GMT

try :

Not Exists( SDP_MSISDN,MSISDN)

Not Exists( MSISDN,SDP_MSISDN)