Diff data with lookup

anupam · Post by **anupam** » Sat Jun 05, 2004 5:35 am

Hello rcil,

In the hash file u might have used id,name and city as the keys which is not at all changing in the source data. The hash file will be having only one entry based on these three keys and the associated date will be reflected in the output.

I don't think that the output u want is possible using hash file unless u have some unique key.

rcil · Post by **rcil** » Sat Jun 05, 2004 8:18 am

Yes, I am using those three fileds as the key columns. If it doesn't work in that way with the hash files then I guess there should be some other way to solve this kind of problem.

Hope I will get helped with the sulution.

thanks,
rcil

chulett · Post by **chulett** » Sat Jun 05, 2004 8:21 am

This seems like it would be simple to do with a Stage Variable. If 'Initial Entry' is a 0, set the stage variable to the Date field on that line. Use the Stage Variable to populate the flat file output field. Keep using it until you find another 0, then switch it again.

No hash file needed.

rcil · Post by **rcil** » Sat Jun 05, 2004 9:56 am

Thanks Craig.

Please can you be more specific. I understand the stage variables but how do I keep using it until I find another 0. What could be my logic on the stage variable and I guess I have to sort the data before I use any stage variables.

Hope you will help in completing. (SV1 and SV2) are two stage variables

if(SV2)=0 then input.DateEnter else if (sv2<>0) then ???????? else NULL:SV1
input.InitialEntry :SV2

Then I populate the OrinialEntry with SV1 and the constraint will be SV1. How can I compare rest of the columns to be same what I did in my first post.

The hash file what I am creating doesn't have any date restrictions that mean it goes after all the data in the database with IntialEntry 0. But the database where I do a lookup on should take DateEntry >=03/20/2002

My result should be:
Id Name City DateEnter Initial Entry OriginalEntry
123 Hary NY 05/22/2002 1 02/02/2002
123 Hary NY 07/22/2002 1 02/02/2002
123 Hary NY 09/27/2003 0 09/27/2003
123 Hary NY 10/20/2003 1 09/27/2003
123 Hary NY 02/12/2004 1 09/27/2003

if you look at the first record DateEnter is 05/22/2002( which is DateEnter>=03/20/2002) but my orinialEntry column doesn't have any date restriction. It should go back all the way and take the intial entry '0'for that record.

thanks,
rcil

chulett · Post by **chulett** » Sat Jun 05, 2004 10:04 am

Unless I'm really missing something here, which could be the case...

One Stage Variable should be fine. It looks like your input data is already sorted, if it is coming in exactly like you showed in your first post. If not, yes you would have to ensure it was sorted properly. Then, your Stage Variable (assuming it is named SV1) would look like this:

Code: Select all

If link.Initial_Entry = 0 Then link.DateEnter Else SV1

Adjust link names and field names as appropriate. This means, if I see a zero, set the Stage Variable to the Date on that line, otherwise set it to itself... ie, don't change it.

Then simply use SV1 in your output derivation.

Does that help?

rcil · Post by **rcil** » Sat Jun 05, 2004 10:19 am

Thanks Craig, for your quick reply hope that will work but the one you are missing in my previous post is

The hash file what I am creating doesn't have any date restrictions that mean it goes after all the data in the database with IntialEntry 0. But the database where I do a lookup on should take DateEntry >=03/20/2002

My result should be:
Id Name City DateEnter Initial Entry OriginalEntry
123 Hary NY 05/22/2002 1 02/02/2002
123 Hary NY 07/22/2002 1 02/02/2002
123 Hary NY 09/27/2003 0 09/27/2003
123 Hary NY 10/20/2003 1 09/27/2003
123 Hary NY 02/12/2004 1 09/27/2003

if you look at the first record DateEnter is 05/22/2002( which satisfy DateEnter>=03/20/2002) but my orinialEntry column doesn't have any date restriction. It should go back all the way and take the intial entry '0'for that record which is 02/02/2002 (which doesn't satify the DateEnter condition).

thanks,
rcil

chulett · Post by **chulett** » Sat Jun 05, 2004 10:29 am

Don't use a hash file - it's not needed here (unless it is being used for something other than the date handing we're discussing) and it's just adding unneeded complications!

Simply flow your (ordered) data through and use the Stage Variable. Does that sound feasible? Do you really need to do a lookup? It seems to me like everything you need to know is already in your input data...

rcil · Post by **rcil** » Sat Jun 05, 2004 10:43 am

Thanks again Craig. I really appreciate your help.Yes, you are true that I am using the lookup only to find out the OriginalEntry date for that record but that date is not in my input database becuase of the DateEnter filter which is >=03/20/2002 but in the originalEntry column I need the date older than 03/20/2002 for intialEntry='0' which is '02/02/2002'

thanks,
rcil

rcil · Post by **rcil** » Sun Jun 06, 2004 10:22 pm

Is there a different approach to solve the above problem???? Hope there should be one to solve these kind of situations.

thanks,
rcil

ray.wurlod · Post by **ray.wurlod** » Mon Jun 07, 2004 6:43 pm

Can you use a more complex SELECT statement to extract the rows in the first place? Something like:

Code: Select all

SELECT ....  FROM tablename T1
WHERE T1.keycol IN (SELECT T2.keycol FROM tablename T2
                    WHERE T2.initialEntry = 0 
                    AND T2.OriginalEntry > '03/20/2002' 
                    AND T1.keycol = T2.keycol)

rcil · Post by **rcil** » Mon Jun 07, 2004 8:28 pm

Thanks Ray that helped.

rcil