Concatenate strings based on a dynamic table

yiminghu · Post by **yiminghu** » Tue May 24, 2005 5:36 pm

Hi,

I have a very headache problem. I have a sequential file which contains more than 3 million records, every records has around 20 fields. The file contains product information about customers.

Here is the samples

Customer_ID, GAS, Eelectricty,Heating_Protection_Plan, Cooling_Protection_Plan, ....
1000,1, 0, 1, 0,....

The value 1 indicates customer has the product, 0 means not. The requrement is to genearte a producting_holding string (by concacatenating the product that customer owns), , but it is also required the sequence of each product within the string should be decided by a product table.

The sample of product is listed as below

Product sequence
Heating_Protection_Plan 1
Electricity 2
Cooling_Protection_Plan 3
GAS 4
......
For above example, the result would be 'Heating_Protection_Plan GAS", besides, the product table changes every months. I could not figure out any easy way to do in Datastage, any suggestions?

Thanks,

Carol

alhamilton · Post by **alhamilton** » Wed May 25, 2005 1:34 pm

If you use your product sequence table in a multirow lookup, but don't specify a key, you should get all the rows from the table returned in the lookup in the order you want. Then you can string together the product name if there is a 1 on input. Then output that string when the customer id changes.

ranga1970 · Post by **ranga1970** » Wed May 25, 2005 6:27 pm

How about stage variable
variablesum, variable key

where variablesum becomes variablesum:filed:variablekey when field value is 1 or remains variablesum
and the variablekey becomes variablekey+1 when field value is 1 and remains variablekey when field value is 0..........

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu May 26, 2005 2:33 am

You have option to distribute the record and collect them again.

sudharsanan · Post by **sudharsanan** » Sun May 29, 2005 11:52 pm

Hai Sainath,

Can you provide some more inputs on how to distribute and recollect to achieve the result...please provide the logic to implement the same..

Thanks
Sudharsanan A N

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue May 31, 2005 8:58 am

Sudharsanan,

There is no straightforward command or stage that can do what I tried to convey. It is a set of tasks on its own involving few steps and having more information of the requirement.

That is why I left it to be decoded by the reader.