topic Re: SCD Type 2 - Issue while updating the flag in QlikView

SCD Type 2 - Issue while updating the flag

Mon, 05 Aug 2013 11:01:08 GMT

1Hi All,

The requirement is I need to maintain history of a record and flag the new record as Y and the existing records as N. I used the below code but

it was not generating the rowno() and flag ??? Can some one help..

Source table is

CustID, Location

20, Sanjose,'Y'

10, Sanfrancisco, 'Y'

my target table cust.qvd should be as below, refer CustID 10 , for the newly added record Sanfrancisco must be flagged as Y and the old custID 10 to be
flagged as 10

CustID, Location, Flag

10, Princeton, 'N'

20, Sanjose,'Y'

10, Sanfrancisco, 'Y'

Let vMD =Date(Floor(QvdCreateTime('D:\Lab\Cust1.qvd')),'DD/MM/YYYY');

Cust:

Mapping load

CustID, Cust_key as Cust_key

FROM D:\Lab\Cust1.qvd(qvd);

Flag_act_inact:

Mapping load

CustID, 'Y' as flag

Resident Cust;

// Resident Cust;

Inc:

LOAD Applymap('Gen_key',CustID,rowno()) as Cust_key,

CustID,

CustName,

Location,

Last_upd,

applymap('Flag_ACT_INACT',CustID,'N') as flag

FROM

D:\Lab\SCD\Customer.xlsx (ooxml, embedded labels, table is Sheet2)

where Last_upd >= $(vMD) ;

Concatenate

LOAD Cust_key,

CustID,

CustName,

Location,

Last_upd,

flag

FROM D:\Lab\Cust1.qvd(qvd));

Store Inc into d:\Lab\SCD2Cust.qvd;

DROP table Inc;

Regards

Sask

Re: SCD Type 2 - Issue while updating the flag

Tue, 06 Aug 2013 16:03:33 GMT

Hi Sask,

i tried another way to load your scd. Take a look at my example.

HtH

Roland

Re: SCD Type 2 - Issue while updating the flag

Wed, 07 Aug 2013 06:35:02 GMT

Thanks . But I couldn't ;open the attached qvw as am using PE.

You're right , the above code to be changed and I changed, its working properly now. But am doing a lookup from the history data set to Incremental data set (new records) to identify the existing records , if its already there in the incremental data set I flag my existing record as 'N' . By default the new data set (incremental ) will be flagged as 'Y'.

Please review my qvw which I attached it here. In case of any comments/changes reqd , please paste your code here.

Thanks

Sasken

Re: SCD Type 2 - Issue while updating the flag

Wed, 07 Aug 2013 06:59:09 GMT

OK Sasken,

quick and without comments the script (next meeting starts right now):

//QUALIFY *;

Old:

LOAD * Inline [

OldCustID, OldLocation

20, Sanjose,'Y'

10, Princeton, 'Y'

];

//my target table cust.qvd should be as below, refer CustID 10 ,

//for the newly added record Sanfrancisco must be flagged as Y and the old custID 10 to be

//flagged as 10

//CustID, Location, Flag

//10, Princeton, 'N'

//20, Sanjose,'Y'

New:

LOAD * Inline [

NewCustID, NewLocation

10, Sanfrancisco

30, Los Alamos

];

// test data loaded

// could have been in qvds of course

//Result:

//CustID, Location, Flag

//10, Princeton, 'N'

//20, Sanjose,'Y'

//10, Sanfrancisco, 'Y'

//30, Los Alamos, 'Y'

//Load existing data and flag it correctly by looking into the new data

Result:

LOAD

OldCustID AS CustID,

OldLocation AS Location,

if( not Exists(NewCustID, OldCustID), 'Y', 'N') AS Flag

Resident Old

;

//append new data and flag it with 'Y'

LOAD

NewCustID AS CustID,

NewLocation AS Location,

'Y' AS Flag

Resident New

//Where not (Exists(CustID, NewCustID))

;

//DROP Table New, Old;

EXIT Script;

HtH

Rol

Re: SCD Type 2 - Issue while updating the flag

Wed, 07 Aug 2013 11:23:35 GMT

Thanks Ronald. Let me check and revert to you.

Re: SCD Type 2 - Issue while updating the flag

Wed, 07 Aug 2013 11:34:01 GMT

Ronald,

I just checked and included the concatenate in the Result table.

BTW, custid 10 is a Natural key, to address this I generated the artificial key. Its working fine.

Thanks