SCD Type 2 - Issue while updating the flag

[#1]

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

[#2]

Hi Sask,

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

HtH

Roland

[#3]

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

[#4]

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

[#5 · Accepted solution]

Thanks Ronald.   Let me check and revert to you.

[#6]

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