Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try like this
LOAD * , SubField(Count1&','&Count2&','&Count3,',') as Count Inline [
Count1,Count2,Count3
100,101,104
102,103,105
];
Try like this
LOAD * , SubField(Count1&','&Count2&','&Count3,',') as Count Inline [
Count1,Count2,Count3
100,101,104
102,103,105
];
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
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
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
i think you should give the path of loaded table file.
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.
You replace the FROM statement with the SQL statement, like this:
NewTable:
SQL select count(*) as count1 from (select distinct customer_name,x,y,z
LOAD
Count1 as Count, 'Count1' as Count-Name;from table1,table2,table3
where table1.id=table2.id and table2.id=table3.id);
And do the same for the other two loads
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.
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);