topic Re: How to load three tables in QlikView

How to load three tables

Mon, 17 Feb 2014 11:34:00 GMT

Hi all,

I have three table.These table are containing various values.then how to load in three tables to one table and how to join these tables.

Re: How to load three tables

Mon, 17 Feb 2014 11:36:02 GMT

Can u provide Example Data so we can understand clearly

Re: How to load three tables

luis_pimentel — Mon, 17 Feb 2014 11:37:09 GMT

If you search in this community you will find hundreds of threats about loading and joining tables.

Re: How to load three tables

salto — Mon, 17 Feb 2014 11:38:43 GMT

Hi Mani,

do the tables have the same fields? If so, Qlik will concatenate them automatically.

If not you will need to concatenate them explicitly with the concatenate command.

Would you please let us know the fields in the tables so we can help you more?

Cheers.

Re: How to load three tables

MayilVahanan — Mon, 17 Feb 2014 11:39:21 GMT

If there is many common fields in each table then create "Key" field by using autonumber function and join with another table.

If there is a common field, then use that field for join.

Re: How to load three tables

Mon, 17 Feb 2014 11:41:05 GMT

You can use following ways:

1) You can use associative join , which qlikview automatically creates. This done by keeping same column name in tables to be joined. Eg:

Table1

Name,

Account

Table2

FirstName,

Phone

Then to join Table1 and Table2, just rename the Table2 FirstName column to Name like

Table2

FirstName as Name,

Phone

2) Use JOIN keyword.

3) You can concatenate the 3 tables into one.

Thanks,

Angad

Re: How to load three tables

Mon, 17 Feb 2014 11:51:23 GMT

Hi Salto,

Please find the sample image,These tables are should not have unique value then how to load these tables to one table, and how to mention it which is matching,Not matching and Rejected values.

Re: Re: How to load three tables

salto — Mon, 17 Feb 2014 11:57:05 GMT

Status	ID
Matching	GK53391
Matching	GK53392
Matching	GP79790
Matching	GS53737
Not_Matching	GI84088
Not_Matching	...
Not_Matching	GW16031
Rejected	GE...
Rejected	GV07018

Is this what you need?

Re: How to load three tables

Mon, 17 Feb 2014 12:02:38 GMT

Hi Mani,

You must think that the tables in qlikview will be joined by all fields that the names be the same between the tables. So using the sentence JOIN above each load of second and third table, looking the fields with the same name are only the fields you like to join.

As Luis says you can find a lot of information in community to pursue the subject.

I hope to be helpful to you.

Regards.

Re: How to load three tables

Mon, 17 Feb 2014 12:06:15 GMT

Hi Salto,

Yes i want this,, Please give solution

Re: How to load three tables

salto — Mon, 17 Feb 2014 14:18:04 GMT

Hi Mani,

I assume that the original data is like this: three different tables:

One with Matching values (named Matching_table)
Other with Not_Matching values (named Not_Matching_table)
Othe with Rejected values (named Rejected_table)

The way to load the three tables in a single Result_Table would be:

Result_Table:

Load

'Matching' as Status,

Value as ID

From Matching_table;

Load

'Not_Matching' as Status,

Value as ID

From Not_Matching_table;

Load

'Rejected' as Status,

Value as ID

From Rejected_table;

Hope this helps!

Re: How to load three tables

salto — Tue, 18 Feb 2014 07:42:06 GMT

Hello Mani,

did you have the chance to try the proposed solution?

Regards!

Re: How to load three tables

Tue, 18 Feb 2014 08:01:39 GMT

If you want some more information about Joins, read about it here: Understanding Join, Keep and Concatenate

Best Regards,

Lucas

Re: How to load three tables

Tue, 18 Feb 2014 08:12:08 GMT

I can't load this tables.Because this a temporary Table.In three tables are converted from Two tables.My Script is,

Table1:

LOAD

@1 as key,

@5, SubField(@5,'-',1) as Field1

FROM

D:\QlikView\POC\Test1.txt

(txt, codepage is 1252, no labels, delimiter is spaces, msq, header is 1 lines);

join

Table2:

LOAD

@1 as key,

@7, SubField(@7,'.',1) as Field2

FROM

D:\QlikView\POC\Test2.txt

(txt, codepage is 1252, no labels, delimiter is ' ', msq, header is 1 lines);

Matching:

load

Field1,Field2as Matching

resident Table1

where Field1= Field2;

Not_Matching:

LOAD

Field2 as Not_Matching

resident Table1

where not Exists(Field1,Field2);

join

LOAD

Field1 as Not_Matching

resident Table1

where not Exists(Field2,Field1);

Cancel:

LOAD

@1 as key,

@4 as Rejected

FROM

D:\QlikView\POC\Test1.txt

(txt, codepage is 1252, no labels, delimiter is spaces, msq, header is 1 lines);

anything else,

Re: How to load three tables

salto — Tue, 18 Feb 2014 08:27:33 GMT

Hi,

Just put this after you script:

Result_Table:

Load

'Matching' as Status,

Field2 as ID

From Matching;

Load

'Not_Matching' as Status,

Field2 as ID

From Not_Matching;

Load

'Rejected' as Status,

Rejected as ID

From Cancel;

drop tables Matching, Not_Matching, Cancel;

Hope this helps!

Re: How to load three tables

Tue, 18 Feb 2014 08:59:40 GMT

when i run the script, i got the following error,

Re: How to load three tables

salto — Tue, 18 Feb 2014 09:03:21 GMT

Sorry, my fault (please replace FROM with RESIDENT):

Result_Table:

Load

'Matching' as Status,

Field2 as ID

resident Matching;

Load

'Not_Matching' as Status,

Field2 as ID

resident Not_Matching;

Load

'Rejected' as Status,

Rejected as ID

resident Cancel;

drop tables Matching, Not_Matching, Cancel;

Re: How to load three tables

Tue, 18 Feb 2014 09:03:32 GMT

This means you need to define the path for the file you're importing your information from! (e.g. From D:\MyFiles\Matching.xls) Otherwise your script should be correct.

Greetings,

Lucas

EDIT: Didn't see the whole conversation before, sorry

Re: How to load three tables

er_mohit — Tue, 18 Feb 2014 09:08:43 GMT

Load your table with concatenate function and add the one field for three table

table1:

load A,

'Matching' as StatusField from table1;

concatenate(table1)

table2:

load C,

'Non-Matching' as StatusField from table2;

concatenate(table1)

table3:

load E,

'Rejected' as StatusField from table3;

Re: How to load three tables

Tue, 18 Feb 2014 10:55:00 GMT

Thanks Jose,

Your Script is correct Solution for my Scenario,Thank you for your Commend.