topic Re: Concatenate on several fields not work in QlikView

Concatenate on several fields not work

Mon, 01 Jun 2015 03:30:08 GMT

Hi,

I have a question, i have two tables let's say table A and table B. Table A has 4 fields,

KEY YEAR WEEK YWEEK

... . .... .... ....

Table B also has 4 fields like table A, i want to concate table A with table B. Field KEY and YWEEK from table B successfuly concate to table A, but FIeld YEAR and WEEK from table B not working. do you know the problem ? Really appreciate your help.

Thanks

UPDATE:

When i convert WEEK from table B using "NUM" function, the concate not working. But when i use WEEK as TEXT it's working. But i need to convert WEEK into number format.

Regards,

Indra

Re: Concatenate on several fields not work

miranrai — Mon, 01 Jun 2015 03:43:03 GMT

Hi,

If two tables are having same columns in both table then it will auto concatenate both table. Qlikview is case sensitive check your column names.

Re: Concatenate on several fields not work

Mon, 01 Jun 2015 03:50:29 GMT

Hi Mira,

Column name is same. Field WEEK on table A has number format, in the other side field WEEK on table B has text format. So i change WEEK on table B into number format using NUM function, the result is not working. but when i'm not convert WEEK on table B into number format (still text format) it's work. Can you give me the solution ?

Thanks

Re: Concatenate on several fields not work

prabhas277 — Mon, 01 Jun 2015 03:50:56 GMT

give some sample data then that will help to you..

Re: Concatenate on several fields not work

vardhancse — Mon, 01 Jun 2015 03:52:37 GMT

Table_A:

LOAD *[Field

KEY

YEAR

WEEK

YWEEK

];

Concatenate

LOAD *[Field

KEY

YEAR

WEEK

YWEEK

];

Now automatically the Table A and Table B will be concatenated based on the 4 fields.

Re: Concatenate on several fields not work

Mon, 01 Jun 2015 03:58:43 GMT

Hi Sasi,

I have try your solution, but it's not the problem.

Field WEEK on table A has number format, in the other side field WEEK on table B has text format. So i change WEEK on table B into number format using NUM function, the result is not working. but when i'm not convert WEEK on table B into number format (still text format) it's work. Can you give me the solution ?

Thanks

Re: Concatenate on several fields not work

Mon, 01 Jun 2015 04:01:29 GMT

Hi Indra,

Try like this

Table_A:

LOAD *[Field

KEY

YEAR

WEEK

YWEEK

];

Concatenate

LOAD *[Field

KEY

YEAR

Num(WEEK) as WEEK,

YWEEK

];

Re: Concatenate on several fields not work

Mon, 01 Jun 2015 04:04:39 GMT

Hi Harsha.

Thanks for the solution, but i have try your solution and it's not working

Another field (KEY, YEAR, YWEEK) sucessfully concate but week is not working and give the result - (stripe symbol)

Do you know the problem ?

Thanks

Re: Concatenate on several fields not work

Mon, 01 Jun 2015 04:28:41 GMT

I think it is creating NULL values(stripe symbols) because of Num(WEEK) ...

let me know the values of WEEK field in Table B.....

Generally NUM(WEEK(Timestamp)) will give you the corresponding numeric value for the given week...

Re: Concatenate on several fields not work

Mon, 01 Jun 2015 04:34:20 GMT

Values of week in Tabel B ex: 01, 02, 03,.... 10, 11, 12, ...., 52

I think the problem in on converting text to number.

Re: Concatenate on several fields not work

PrashantSangle — Mon, 01 Jun 2015 04:37:39 GMT

Hi ,

Try like this

Table_A:

LOAD *[Field

KEY

YEAR

WEEK

YWEEK

];

Concatenate

LOAD *[Field

KEY

YEAR

Num(Num#(WEEK)) as WEEK,

YWEEK

];

Regards

Re: Concatenate on several fields not work

Mon, 01 Jun 2015 05:10:28 GMT

Hi Indra,

It's is simple, you have to learn date format then it's simple....

Do like this:

Table_A:

LOAD *

KEY,

YEAR,

WEEK,

YWEEK

;

Concatenate

LOAD *

KEY,

YEAR,

Num(WEEK) as WEEK,

YWEEK

;

Ramya.

Re: Concatenate on several fields not work

Mon, 01 Jun 2015 05:18:13 GMT

Make sure Datatype of fields in both the tables are same. If not convert it to same data type.

Re: Concatenate on several fields not work

Mon, 01 Jun 2015 06:06:45 GMT

Yes...

Can you please share the values for the WEEK field in Table A?

Re: Concatenate on several fields not work

PrashantSangle — Mon, 01 Jun 2015 06:11:58 GMT

Hi,

for converting text to Num

use Num#() and Num()

like NUM(NUM#(FieldName,'00'),'00')

Regards

Re: Concatenate on several fields not work

Mon, 01 Jun 2015 06:41:47 GMT

Hi,

For number formatting we will use like NUM(NUM#(FieldName,'00'),'00')

And for converting the Text to Numeric value we must should specify which one have to convert means Month or week or date etc..

Num(Month(Date)) or

NUM(Week(Timestamp)) or

Num(Date(Today())

Otherwise it will give NULL values

Re: Concatenate on several fields not work

sasiparupudi1 — Mon, 01 Jun 2015 07:57:12 GMT

Try

Table_A:

LOAD * Inline [KEY,YEAR,WEEK,YWEEK

1,2002,1,20021

2,2002,2,20022

3,2002,3,20023

4,2002,4,20024

5,2002,5,20025

];

noConcatenate

Table_B:

LOAD * Inline [KEY,YEAR, WEEK ,YWEEK

100,"2002","01",200201

101,"2002","02",200202

102,"2002","03",200203

103,"2002","04",200204

104,"2002","05",200205

];

NoConcatenate

LOAD * Resident Table_A;

Concatenate

LOAD KEY,YEAR,Num(WEEK) as WEEK,YWEEK Resident Table_B;

drop table Table_A;

drop table Table_B;

Re: Concatenate on several fields not work

timanshu — Mon, 01 Jun 2015 11:09:51 GMT

Can you upload screenshot of fields creating problem from both tables fetched separately(no concatenation), using table viewer.?

Re: Concatenate on several fields not work

Wed, 03 Jun 2015 07:17:52 GMT

Hi Max

Sorry for late reply.

Wow it solved my problem Max,

Thanks for your help

Regards,

Indra

Re: Concatenate on several fields not work

PrashantSangle — Wed, 03 Jun 2015 07:31:19 GMT

Welcome