topic Re: how to create one field from the table based on 2 different fields in QlikView

how to create one field from the table based on 2 different fields

Mon, 03 Nov 2014 08:50:01 GMT

Hi,

i have 2 fields in one table, need to derive one more field based on the 2 existing fields, please suggest me how to achieve this.

Re: how to create one field from the table based on 2 different fields

simenkg — Mon, 03 Nov 2014 08:52:31 GMT

Field1 + Field2 as Field3, //Returns the sum of the two fields

Field1 & Field2 as Field4, //Returns the concatenated string of the two fields.

You should get the picture.

Re: how to create one field from the table based on 2 different fields

Mon, 03 Nov 2014 08:52:42 GMT

Hi Savi,

a bit more detail on what you are trying to get as your end result would be good.

If you are just trying to combine the two fields, you can use '&' and alias as a new field.

eg. FieldA & FieldB As Field C

Or with a separator FieldA & '|' & FieldB As FieldC

hope that help

Joe

Re: how to create one field from the table based on 2 different fields

its_anandrjs — Mon, 03 Nov 2014 08:57:37 GMT

Hi,

If this fields are string or number you can concatenate this fields or if conditionally you can to create it you can do this in the load script

Load

Field1,

Field2,

Field1&'-'&Field2 as Newfield

From Location;

Load

Field1,

Field2,

If(Field1=2 and Field2 <= 5,Field1,Field2) as Newfield

From Location;

It depends on the conditions. Provide if you have any sample date.

Regards

Anand

Re: how to create one field from the table based on 2 different fields

Kushal_Chawda — Mon, 03 Nov 2014 09:01:44 GMT

what exactly you want to calculate?

Re: how to create one field from the table based on 2 different fields

Mon, 03 Nov 2014 09:14:08 GMT

Re: how to create one field from the table based on 2 different fields

anbu1984 — Mon, 03 Nov 2014 09:17:06 GMT

For field2 <> b, how do you derive output?

Re: how to create one field from the table based on 2 different fields

its_anandrjs — Mon, 03 Nov 2014 09:20:35 GMT

Write like

Load

Field1,

Field2,

if( Field2 = 'b','y') as OP

From Location;

With your snap shot not understood provide any sample for this in any inline table or excel file for exlanation

Regards

Anand

Re: how to create one field from the table based on 2 different fields

Mon, 03 Nov 2014 09:23:54 GMT

if field2 has 'b' then for all field1 records having 'x' o/p should be 'Y'

if field2 doesn't have 'b' then for all field1 records having 'x' o/p should be 'N'

Re: how to create one field from the table based on 2 different fields

its_anandrjs — Mon, 03 Nov 2014 09:38:32 GMT

Why not you directly check the Field1 for this.

or you can try

LOAD Field1,Field2,if(Field2='b','Y','N') as Flag,RowNo() as RID;

LOAD * Inline

[

Field1,Field2

x,a

x,b

y,a

y,b

]

Regards

Anand

Re: how to create one field from the table based on 2 different fields

anbu1984 — Mon, 03 Nov 2014 09:39:23 GMT

Tbl1:

Load * Inline [

Field1,Field2,Field3

x,a,12

x,b,11

y,a,13

y,b,11 ];

Left Join(Tbl1)

Load Field1,If(Sum(Field1='x' And Field2='b'),'Y','N') As Op Resident Tbl1 Group by Field1;