How to write script to change multiple ID's to one ID

[#1]

Hi everyone,

I have a column which contains ID's and anothet column which contains names

for eg: ID=23487 and name='house', ID=23876 and name='house

           ID=23444 and name='office', ID=23823 and name='office'

I need to use these two columns in one Straight table so that it will appear side by side. So, when I am trying to match both ID's and names it is appearing as below. 

ID	Name
23487	house
23876	house
23444	office
23823	office

But I would like to see my data as

ID	Name
23876	house
23823	office

what I mean is I need to change 23487 to 23876 and 23444 to 23823 and so on. Could anyone tell me how it will possible to make these changes in qvw file. how to write the script for this?

Thanks in advance.

[swuehl]

You want to do this in the script, right? What would be the rule for merging the ID values, max value per Name?

Maybe like this:

MergedIDTable:

NOCONCATENATE LOAD

     Name,

     max(ID) as ID

RESIDENT YourTable GROUP BY Name;

DROP TABLE YourTable;

[#3]

Hi swuehi,

Yes I want to writethis in script but I have to use this table in separate tab in script in thsi how can I use Concatenate and Resident? Is there any way to use without Concatenate and Resident? Please tell me .

Thanks.

[swuehl]

Above was assuming your data is already loaded into your data model, i.e. a resident table.

The essential idea is using a GROUP BY load and max(ID), so you can use it also like this without a resident load and noconatenate, grouping your data when loading in:

LOAD Name,

          max(ID) as ID

INLINE [

ID,          Name

23487,          house

23876,          house

23444,          office

23823,          office

] group by Name;

Instead of the INLINE load, you can load your data from different source, of course. But you'll need to add some more details on your setting to get additional help on the syntax.

[#5]

Thanks for this.

But to write the inline statement It's difficult to enter each ID and Name as I have huge data. In this case how I needto proceed.

Thanks.

[calvindk]

NormalTable:

Load * // Will link on Name

From NormalSource;

ID_Table:

Load Max(ID) as OneID, Name // Will link on Name

Group By Name

Resident NormalTable;

--------

Straight table

OneID, Name, Expressions

This way you don't overwrite the original ID

[#7]

I am getting error "Aggregation expressions required by GROUP BY clause" when I used Group by name.Actually I need to consider ID from one table and Name from another table. In this case shall I proceed like above?

Please help it's very urgent