load data,Incremental data,SCD

[#1]

1. I want to load data from  database with extra columns into QVD file can any one help me on this.

LOAD

"PrimeId",

"Food Category",

"Food Item",

"Loaction;

SQL SELECT *

FROM "table1";

store table1 into 'D:\abc.qvd';

with this I required extra bellowed columns also into QVD

Start_date,

End_date,

Scd_version,

Scd_active

2. This as same like scd concept but my requirement should as below.

For suppose if record  is loading for the first time then the start_date should be current date i.e system date, End_date is null,scd_version is 1 and scd_active is 1.

If same record is updated then start_date should remains same , End_date is null,scd_version is 2 and scd_active is 1.

If same record is deleted then start_date should remains same , End_date is i.e system date,scd_version is remains same  and scd_active is 0.

     Thanks in advance,

Thanks and Regards,

B.Kasi Viswanath.

[PradeepReddy]

Hi Kasi,

I have few concerns.

1) On which criteria you are saying the record is updated?

2) Suppose for one ID, there is 3 updates, then you need to store all 3 versions into QVD?

Thanks,

Pradeep

[#3]

Thanks for ur replay Pradeep,

If my record is updated for the second load then version should change to 2 if is third then it should be 3 its like

If my record is updated then my version should be incremented by 1 else it should remains same.

example:

PrimeId,

Food Category,

Food Item,

Loaction,

close_date,when that record is closed time

Start_date-when that record is created for first time

End_date,when that record is closed time

Scd_version,increment by 1 for every update of that record

Scd_active ,it record is between the start and end date then it should be active(with 1)

first load

PrimeId,Food Category,Food Item,Loaction,close_date,Start_date,End_date,Scd_version,Scd_active

1,chinese,noodles,veg noodles,china,null,2015-01-01,null,0,1

second load (update record with new changes)

PrimeId,Food Category,Food Item,Loaction,close_date,Start_date,End_date,Scd_version,Scd_active

1,chinese,maggi,veg noodles,india,null,2015-01-01,null,1,1

third load (update record with new changes)

PrimeId,Food Category,Food Item,Loaction,close_date,Start_date,End_date,Scd_version,Scd_active

1,chinese,topraman,veg noodles,india,null,2015-01-01,null,2,1

forthload (close date)

PrimeId,Food Category,Food Item,Loaction,close_date,Start_date,End_date,Scd_version,Scd_active

1,chinese,topraman,veg noodles,india,2015-01-09,2015-01-01,2015-01-09,2,0

[PradeepReddy]

The atatched application might be helpfull.  Let me know if not.

[#5]

I did not find any attachment here pradeep

[PradeepReddy]

try to open thread in new window....