Want to create new field with data of 3 existing fields

[#1]

Hello Friends,

I have 3 different fields count1,count2 and count3. Now, i want all these 3 fields under one field  "Count"

I have the following fields

Count1     Count2       Count3

-----------   -------------   -----------------

125            231            98

Now i want

Count-Name   Count

---------------------------------

  Count1           125

  Count2           231

  Count3            98

Any help is greatly appreciated.

[sivarajs · Accepted solution]

Try like this

LOAD * , SubField(Count1&','&Count2&','&Count3,',') as Count Inline [

Count1,Count2,Count3

100,101,104

102,103,105

];

[jfkinspari]

You could use several concatenate loads, to do the job

NewTable:
LOAD
Count1 as Count, 'Count1' as Count-Name
FROM Original table
Concatenate LOAD
Count2 as Count, 'Count2' as Count-Name
FROM Original table
Concatenate LOAD
Count3 as Count, 'Count3' as Count-Name
FROM Original table

[#4]

use this expression in script

LOAD * ,

SubField(Count1 & ',' & Count2 & ',' & Count3,',') as count Inline [

                 Count1,Count2,Count3

                  125,231,98

];

the output comes like this,

Count-Name   count

---------------------------------

  Count1           125

  Count2           231

  Count3            98

[#5]

Thanks Jens Frederik.

But what should i give for "Original table" ?

I got the count1 by joining multiple tables as

select count(*) as count1 from (select distinct customer_name,x,y,z

from table1,table2,table3

  where table1.id=table2.id and table2.id=table3.id);

Similarly for count2 and count3

[#6]

i think you should give the path of loaded table file.

[#7]

Hi Vishwaranjan, Your reply definitely helps

But the Values 125,231,98 are not fixed they are generated at run-time.

The "Count" values keep changing. They are just stored in count1, count2 and count3 fields.

You can refer my reply in above post for the query that i have used.

[jfkinspari]

You replace the FROM statement with the SQL statement, like this:

NewTable:
LOAD
Count1 as Count, 'Count1' as Count-Name;
SQL select count(*) as count1 from (select distinct customer_name,x,y,z
from table1,table2,table3 
where table1.id=table2.id and table2.id=table3.id);

And do the same for the other two loads

[#9]

Hi Sivaraj, Thanks for your reply.  It is working.

But the Values 125,231,98 are not fixed they are generated at run-time.

The "Count" values keep changing. They are just stored in count1, count2 and count3 fields.

You can refer my reply in below post for the query that i have used.

[#10]

thanks arunpaul.

try this like

load count1,count2,count3

FROM

C:\Desktop\test27.xlsx

(ooxml, embedded labels, table is Sheet1);

load count1 as count, 'count1' as count_name

FROM

C:\Desktop\test27.xlsx

(ooxml, embedded labels, table is Sheet1);

Concatenate load count2 as count, 'count2' as count_name

FROM

C:\Desktop\test27.xlsx

(ooxml, embedded labels, table is Sheet1);

Concatenate load count3 as count, 'count3' as count_name

FROM

C:\Desktop\test27.xlsx

(ooxml, embedded labels, table is Sheet1);