topic Re: Joins in QlikView

Joins

Wed, 25 Feb 2015 05:02:26 GMT

I have attached a Qlikview file with the following tables:

Test:

load * inline

[Entry No, Ref

101, 106

102, 107

103, 108

104, 109

105, 110];

Number:

load * inline

[Entry No, ItemNo

101, 11123

102, 11124];

Number1:

load * inline

[Entry No, ItemNo

103, 11125

104, 11126

105, 11127];

Desc:

load * inline

[ItemNo, Desc

11123, AAA

11124, BBB

11125, CCC

11126, DDD

11127, EEE];

I want the output as follows:

But it should be loaded as a single file. for example, i can link the common fields and prepare a new table.

But i want to use concatenate and joins and then load as a single file.

i am able to do if i have only one 'number' table. because i have two tables - (number and number1), i am not able to do it.

Re: Joins

PradeepReddy — Wed, 25 Feb 2015 05:21:23 GMT

please find the attached application...

Re: Joins

Wed, 25 Feb 2015 05:27:48 GMT

Hi kartik,

Please find the below script

Test:
load * inline
[Entry No, Ref
101, 106
102, 107
103, 108
104, 109
105, 110];

Number:
load * inline
[Entry No, ItemNo
101, 11123
102, 11124];

Number1:
load * inline
[Entry No, ItemNo
103, 11125
104, 11126
105, 11127];

Left Join(Test)
LOAD
*
,'aa' as flag
Resident Number;
DROP Table Number;

left Join(Test)
Desc:
load * inline
[ItemNo, Desc
11123, AAA
11124, BBB
11125, CCC
11126, DDD
11127, EEE];

DROP Field flag from Test;

Regards

$@M

Re: Joins

Wed, 25 Feb 2015 05:30:58 GMT

Hi Kartik,

Please find the attached file. I guess you wanted it as a single table.

I am getting the output as required by you.

Hope it helps.

Regards,

Shashidhar

Re: Joins

jagan — Wed, 25 Feb 2015 05:33:46 GMT

Hi,

Try this script

Test:

load * inline

[Entry No, Ref

101, 106

102, 107

103, 108

104, 109

105, 110];

Number:

load * inline

[Entry No, ItemNo

101, 11123

102, 11124];

Concatenate(Number)

load * inline

[Entry No, ItemNo

103, 11125

104, 11126

105, 11127];

Desc:

load * inline

[ItemNo, Desc

11123, AAA

11124, BBB

11125, CCC

11126, DDD

11127, EEE];

Now use Table box to display all the fields and you will get the required format. If you want you can Join all the tables and convert it into single table.

Regards,

Jagan.

Re: Joins

Wed, 25 Feb 2015 05:37:50 GMT

But Jagan,

It has three tables linked to one another.

I wanted a single table.

Re: Joins

Wed, 25 Feb 2015 05:38:59 GMT

Hi,

PFA,

Re: Joins

Wed, 25 Feb 2015 05:42:13 GMT

hi karthik

see the script what I have posted.

it gives you what you want exactly.

Regards

$@M

Re: Joins

PradeepReddy — Wed, 25 Feb 2015 05:44:12 GMT

check the application which I posted earlier.. hope it caters your requirement..

Re: Joins

PrashantSangle — Wed, 25 Feb 2015 05:47:51 GMT

Hi,

Try like,

Number:

load * inline

[Entry No, ItemNo

101, 11123

102, 11124];

Concatenate

load * inline

[Entry No, ItemNo

103, 11125

104, 11126

105, 11127];

join

load * inline

[ItemNo, Desc

11123, AAA

11124, BBB

11125, CCC

11126, DDD

11127, EEE];

Final

load * inline

[Entry No, Ref

101, 106

102, 107

103, 108

104, 109

105, 110];

join

load *

Resident Number;

Drop table Number;

Regards

Re: Joins

jagan — Wed, 25 Feb 2015 05:58:01 GMT

Hi,

Qlikview internally manages the joins as long as the column name are same, why you are worrying about that. It is good practice to create star schema instead of single table, sometimes single table may cause duplication of records.

Any way if you want to join into a single table you can use below script

Test:

load * inline

[Entry No, Ref

101, 106

102, 107

103, 108

104, 109

105, 110];

Number:

load * inline

[Entry No, ItemNo

101, 11123

102, 11124];

Concatenate(Number)

load * inline

[Entry No, ItemNo

103, 11125

104, 11126

105, 11127];

LEFT JOIN (Test)

LOAD

RESIDENT Number;

DROP TABLE Number;

LEFT JOIN (Test)

load * inline

[ItemNo, Desc

11123, AAA

11124, BBB

11125, CCC

11126, DDD

11127, EEE];

HOpe this helps you.

Regards,

Jagan.

Re: Joins

Wed, 25 Feb 2015 06:44:56 GMT

Hi..

See the attached script:

Number:

load * inline

[Entry No, ItemNo

101, 11123

102, 11124];

Concatenate

Number1:

load * inline

[Entry No, ItemNo

103, 11125

104, 11126

105, 11127];

Inner Join(Number)

Test:

load * inline

[Entry No, Ref

101, 106

102, 107

103, 108

104, 109

105, 110];

Inner Join(Number)

Desc:

load * inline

[ItemNo, Desc

11123, AAA

11124, BBB

11125, CCC

11126, DDD

11127, EEE];