Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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.

Tags (1)
1 Solution

Accepted Solutions
sivarajs
Valued Contributor II

Re: Want to create new field with data of 3 existing fields

Try like this

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

Count1,Count2,Count3

100,101,104

102,103,105

];

13 Replies
sivarajs
Valued Contributor II

Re: Want to create new field with data of 3 existing fields

Try like this

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

Count1,Count2,Count3

100,101,104

102,103,105

];

jfkinspari
Contributor III

Re: Want to create new field with data of 3 existing fields

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

Re: Want to create new field with data of 3 existing fields

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

Re: Want to create new field with data of 3 existing fields

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

Re: Want to create new field with data of 3 existing fields

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

Not applicable

Re: Want to create new field with data of 3 existing fields

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
Contributor III

Re: Want to create new field with data of 3 existing fields

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

Re: Want to create new field with data of 3 existing fields

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

Re: Want to create new field with data of 3 existing fields

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

Community Browser