Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Outer join and populate missing data

I've got two tables like this:

TaskData:

TaskIDCountry
1Finland
2Sweden

TaskStatus:

TaskIDDateStatus
12012New
12013In Progress
12014Closed
32014New

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:

TaskIDCountryDateStatus
1Finland
12012New
12013In Progress
12014

Completed

2Sweden
32014New

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.

7 Replies
Not applicable

Can u provide ur sample file??

MK_QSL
MVP
MVP

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

];

Not applicable

Find attachment.

lironbaram
Partner
Partner

you can use simple join

it will do the work for you

have a look at the attach file

Not applicable

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.

its_anandrjs

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

TaskIDCountryDateStatus
1Finland2012New
1Finland2013In Progress
1Finland2014Closed
3 2014New

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

TaskIDCountryDateStatus
1Finland2012New
1Finland2013In Progress
1Finland2014Closed
2Sweden
3 2014New

Let me know about

MK_QSL
MVP
MVP

Hi Gustav,

Have you tried my reply?