DSXchange

Hi,
please help me in solving this case study...

This is the scenario to be consider to implement scd 2 in my case study,
i have the following columns with the data in the input sequential file...

-->input_sequential file
----------------------------
contrid contlineid product_name start_date end_date Lic_cunt
10 1 a1 1/2/08 6/9/08 10
10 1 a1 4/4/08 6/9/08 20
10 1 a1 5/6/08 6/9/08 30
20 2 a2 31/6/08 8/12/08 80
20 2 a2 07/9/08 8/12/08 100
20 2 a2 22/11/08 8/12/08 180
------------------------------------------------------------------------------------

The output must be,as follows

*Note: concentrate on start_date and end_date fields

The output :
--------------
contrid contractlineid product_name start_date end_date Lic_cunt
10 1 a1 1/2/08 3/4/08 10
10 1 a1 4/4/08 4/6/08 20
10 1 a1 5/6/08 6/9/08 30
20 2 a2 31/6/08 6/9/08 80
20 2 a2 07/9/08 21/11/08 100
20 2 a2 22/11/08 8/12/08 180
------------------------------------------------------------------------------------
conditions:
1. from the inputfile,you will notice that endate for all contractlineid=1 will be 6/9/08 and for all contractlineid=2 will be 8/12/08. so,when ever the contractlineid=1 ,the previous record of contractlineid=1 end_date field must be the current record start_date - 1 ,you can consider the following example,
input:
contrid contlineid product_name start_date end_date Lic_cunt
10 1 a1 1/2/08 6/9/08 10
10 1 a1 4/4/08 6/9/08 20
10 1 a1 5/6/08 6/9/08 30
output:

contrid contractlineid product_name start_date end_date Lic_cunt
10, 1, a1, 1/2/08 , 3/4/08 , 10
10, 1, a1, 4/4/08 , 4/6/08 , 20
10 1 a1 5/6/08, 6/9/08, 30

you can notice that, in the o/p of first record end_date field changed to
3/4/08 from 6/9/08 i.e. (4/4/08)-1

To get the records in this manner in the end_date,what is the condition i have to implement and whether i have to use stage variable/routine?

If any one help me in getting this output by implementing in datastage,i am thankful to them in advance.... :)

Welcome aboard. :D

It would be best if you told us what parts you are having a problem with or asked some specific questions. Right now, it seems as though you are looking for a 'silver platter' answer, for someone to just lay a full solution out at your feet. That's not generally the nature of the help one comes looking for here.

First thing you should be doing in writing out a specification or approach document, whatever you want to call it. This would be in words, regardless of tool, to lay out the steps needed to solve this little puzzle. Then, figure out how each step can be accomplished in your tool of choice.

Input and output you gave looks like same and moreover nothing to concentrate on date fileds.

Be specific and clear before shooting queries...

Input and output you gave looks like same and moreover nothing to concentrate on date fileds.

Be specific and clear before shooting queries...




regards
Rajesh Devabhaktuni

You need to concentrate... harder. Look closer at the output dates, especially in light of the 'SCD2' topic.

Yes Chullet ...you are doing more concentration than me...good :D

Getting it right first time would have obviated this new requirement. Now you have to fix the expiration dates. As Craig suggested, write out the rules in English (or your native language), and turn this into a source-to-target mapping specification. You will probably find that it comes out easily.
Hint: consider sorting the start_date column in descending order.

Above Casestudy can be solved by using stage variables/routines

Job flow will be as below:

source----->sort stage---->transformer----target


Above in the Sort stage do sort the start_date as desc, contrid as asc(based on the key columns).
In XFM use the stagevariables accordingly ..............



*Note: Above is one way of resolution among several...

regards
Sai

Hi,

source-------lookup--------transformer-------Insert and Update

When we load the record into the dimension table first time.
How can i load record version without Surrogate key,trigger and sequence options.

Please do not post the same question multiple times, and especially do not hijack other's posts. And Server or PX, which is it?

PX post is here:

viewtopic.php?t=121121

What ever happened to G.K.K. (the original poster)?