Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Want to create new field with data of 3 existing fields

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.

1 Solution

Accepted Solutions
sivarajs
Specialist II
Specialist II

Try like this

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

Count1,Count2,Count3

100,101,104

102,103,105

];

View solution in original post

13 Replies
sivarajs
Specialist II
Specialist II

Try like this

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

Count1,Count2,Count3

100,101,104

102,103,105

];

jfkinspari
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

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.

Not applicable
Author

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);