Lookup Condition not correct

us1aslam1us · Post by **us1aslam1us** » Sat Apr 08, 2006 11:21 am

Hi,

I am doing a simple lookup:

Database
v
v
Dataset>>>>Lookup>>>>>Dataset

In the Database i am having the data as

ID POS DEPT
1 QA FIN
2 QA FIN
3 QA FIN
1 BA DIN
3 BA DIN

and the source is having data like

ID Name1 Name2
1 A AA
2 B BB
3 C CC

The key is ID for lookup.I want only 3 rows in the output like

ID POS1 POS2 Name1
1 QA BA A
2 QA B
3 QA BA C

ANY HELP IS APPRECIATED

THANKS
SAM

ray.wurlod · Post by **ray.wurlod** » Sat Apr 08, 2006 3:16 pm

There is no common key on which a lookup can be based. Please describe the algorithm you would use to generate the output if DataStage were not available.

us1aslam1us · Post by **us1aslam1us** » Mon Apr 10, 2006 12:35 am

ray.wurlod wrote:There is no common key on which a lookup can be based. Please describe the algorithm you would use to generate the output if DataStage were not available.

Hi Ray,

I can do this thing by doing the lookup based on ID and allowing "Multiple rows" through the lookup link.

ID POS Name1
1 QA A
1 BA A
2 QA B
3 QA C
3 BA C

After this i am getting stuck on how to convert this multiple rows into a single row.

Any Help is appreciated
Thanks
sam

ray.wurlod · Post by **ray.wurlod** » Mon Apr 10, 2006 1:08 am

This technique is called a vertical pivot - a search will reveal a few approaches that people have taken to it. It's tricky to do in parallel jobs and retain parallelism.

us1aslam1us · Post by **us1aslam1us** » Mon Apr 10, 2006 12:09 pm

Hi

Can somebody tell me what this Error means.

Code: Select all

Lkp_Num_Cards: When checking operator: Operator of type "APT_LUTCreateOp": will partition despite the
preserve-partitioning flag on the data set on input port 0.

Thanks
sam

us1aslam1us · Post by **us1aslam1us** » Mon Apr 10, 2006 12:15 pm

After doing a search i found that by clearing the preserve partion flag this warning is handled.But can anyone help me in understanding why this happened?

Thanks
sam

richdhan · Post by **richdhan** » Tue Apr 11, 2006 12:52 am

Hi Sam,

By default the previous stages would have a setting of propogating the existing partition to the following stages. But you would have defined a new partitioning for the following stage.

So DataStage gives a warning stating that the existing partiton cannot be preserved and rather the new partition will be used for that particular stage.

HTH
--Rich