topic Re: Left join and populate missing data in QlikView

Outer join and populate missing data

Wed, 23 Apr 2014 09:05:18 GMT

I've got two tables like this:

TaskData:

TaskID	Country
1	Finland
2	Sweden

TaskStatus:

TaskID	Date	Status
1	2012	New
1	2013	In Progress
1	2014	Closed
3	2014	New

Now I join these two tables like this:

TaskData:
LOAD
TaskID,
Country
FROM x;

Outer Join (TaskData)
LOAD
TaskID,
Date,
Status
FROM x

When I use the resulting tables I want to be able to filter across both tables. However, when selecting for example "Finland" as Country I get only blank results in Status & Date.

I took a look at the merged table and it looks like this:

TaskID	Country	Date	Status
1	Finland
1		2012	New
1		2013	In Progress
1		2014	Completed
2	Sweden
3		2014	New

My question is; how can I merge these two tables so that I can filter on any of the data? I guess I need to populate the blanks, but how do I do that?

Thanks in advance.

Re: Left join and populate missing data

Wed, 23 Apr 2014 09:08:06 GMT

Can u provide ur sample file??

Re: Outer join and populate missing data

MK_QSL — Wed, 23 Apr 2014 09:11:46 GMT

Load * Inline

[

TaskID, Date, Status

1, 2012, New

1, 2013, In Progress

1, 2014, Closed

3, 2014, New

];

Left Join

Load * Inline

[

TaskID, Country

1, Finland

2, Sweden

];

Re: Outer join and populate missing data

Wed, 23 Apr 2014 09:18:34 GMT

Find attachment.

Re: Outer join and populate missing data

lironbaram — Wed, 23 Apr 2014 09:19:27 GMT

you can use simple join

it will do the work for you

have a look at the attach file

Re: Outer join and populate missing data

Wed, 23 Apr 2014 12:07:09 GMT

Thank you all for your answers. I didn't know I could do a simple Join, but unfortunately that didn't help.

In the end though, I realised I might as well have these in different table and just link them via the TaskID. This works.

Re: Outer join and populate missing data

its_anandrjs — Wed, 23 Apr 2014 12:22:38 GMT

You can use Left join or either Outer join for perfect result and let me know how you need the out put from the joining see the sample

1. First way of doing this with Left join

LOAD * INLINE [

TaskID, Date, Status

1, 2012, New

1, 2013, In Progress

1, 2014, Closed

3, 2014, New];

Left Join

LOAD * INLINE [

TaskID, Country

1, Finland

2, Sweden];

Then you get this table

TaskID	Country	Date	Status
1	Finland	2012	New
1	Finland	2013	In Progress
1	Finland	2014	Closed
3		2014	New

2. Second way is using outer join

LOAD * INLINE [

TaskID, Country

1, Finland

2, Sweden];

Outer Join

LOAD * INLINE [

TaskID, Date, Status

1, 2012, New

1, 2013, In Progress

1, 2014, Closed

3, 2014, New];

Then you get this table

TaskID	Country	Date	Status
1	Finland	2012	New
1	Finland	2013	In Progress
1	Finland	2014	Closed
2	Sweden
3		2014	New

Let me know about

Re: Outer join and populate missing data

MK_QSL — Wed, 23 Apr 2014 12:43:13 GMT

Hi Gustav,

Have you tried my reply?