topic Re: Merge fields to an unique field in QlikView

Merge fields to an unique field

Wed, 16 Apr 2014 07:18:44 GMT

Hello

I am facing a problem where I dont find a solution for it, but I am pretty sure that there is one

I have field from different sources which I want to combine to an unique field. This unique field is my link to value from another table.

Example:

Region

from table1

Code

from table2

Year

from table3

unique_field

value
from table 4

the unique_field is a combination of 'Region+Code+Year' how do I create it in the load script so that I have the link to table 4 and can call the value field from table 4?

THANKS for your support!!

Regards

Martin

Re: Merge fields to an unique field

MK_QSL — Wed, 16 Apr 2014 07:25:46 GMT

can you provide some sample data? may be 4-5 lines?

Re: Merge fields to an unique field

Wed, 16 Apr 2014 07:37:32 GMT

Region

GCC
NE

Code

12345

54321

Year

2013

2014

and the unique_code should be then

GCC123452013

GCC123452014
GCC543212013

GCC543212014

NE123452013

NE123452014

NE543212013

NE543212014

and for this unique_code is a value in another table which I want to call

Re: Merge fields to an unique field

maxgro — Wed, 16 Apr 2014 07:45:14 GMT

Region:

load * inline [

Region

GCC

];

Code:

load * inline [

Code

12345

54321

];

Year:

load * inline [

Year

2013

2014

];

tmp:

noconcatenate load * resident Region;

join (tmp) load * Resident Code;

join (tmp) load * Resident Year;

final:

NoConcatenate load Region & Code & Year as Unique, * Resident tmp;

DROP Table tmp;

Re: Merge fields to an unique field

Wed, 16 Apr 2014 07:45:47 GMT

Try to use CROSS JOIN for three tables and then

load

Region&Code&Year as unique_code

resident [table_from_cross_join];

Re: Merge fields to an unique field

arsal_90 — Wed, 16 Apr 2014 07:48:33 GMT

Can you provide all fields in all dimension table as well in transaction table.

For your problem there is two way of solution one through Link Table and another through concatenation but it depends on situation

Re: Merge fields to an unique field

Michiel_QV_Fan — Wed, 16 Apr 2014 08:27:31 GMT

What is CROSS JOIN? I didn't find that function in QlikView help.

Re: Merge fields to an unique field

anbu1984 — Wed, 16 Apr 2014 08:52:53 GMT

You can use Join to do Cross Join provided there is no common fields between tables

Check the script above provided by Massimo is an example for cross join