Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

kevpickering
New Contributor III

Left Join in Partial Reload

Hi,

I'm not sure if what I'm trying to achieve is possible. I'm trying to incorporate a LEFT JOIN on a resident table that has been Replaced in a Partial Reload. The Replace and Left Join are required in both Full and Partial reloads.

Here's a simple script to test it out:-

Teams:

REPLACE LOAD * INLINE [

     Team, Code

     Wigan, W1

     Dracs, D1

     Rhinos, R1

];

LEFT JOIN (Teams)

LOAD * INLINE [

     Code, Color

     W1, Cherry and White

     D1, Red and Gold

     R1, Blue and Amber

];

Venues:

LOAD * INLINE [

     Code, Venue

     W1, DW Stadium

     D1, Stade de Gilbert

     R1, Headingley

];

When I execute Reload, everything is fine, 3 tables are loaded and table viewer shows 3 tables as expected.

When I execute a Partial Reload, the data model now has synthetic keys with an extra table of Teams-1 added. What I am hoping to achieve is that the Teams table would be replaced, the extra field added with the LEFT JOIN and the data model of 3 tables remaining.

This is a very simple script to test the methodology, my actual script is a bit more complex, but if I can't get the above to work..

Is a LEFT JOIN to a replaced RESIDENT table possible during a partial reload?

Tags (1)
1 Solution

Accepted Solutions
kevpickering
New Contributor III

Re: Left Join in Partial Reload

Well, I've managed to achieve it....

The successful script....

IF IsPartialReload() THEN

     DROP TABLE Teams;

END IF;

Teams:

ADD LOAD * INLINE [

     Team, Code

     Wigan, W1

     Dracs, D1

     Rhinos, R2

];

LEFT JOIN (Teams)

ADD LOAD * INLINE [

     Code, Color

     W1, Cherry and white

     D1, Red and Gold

     R2, Blue and Amber

];

Venues:

LOAD * INLINE [

     Code, Venue

     W1, DW Stadium

     D1, Stade de Gilbert

     R2, Headingley

];

Note the explicit dropping of the table in Partial reload, followed by REPLACE ADD LOAD to recreate the Teams table and then LEFT JOIN ADD LOAD to add the extra Color field.

Thanks all for your input.

4 Replies

Re: Left Join in Partial Reload

Hi,

In your posted script there is only 2 tables how you are getting 3 table?

and If you are using Resident table then write

NoConcatenate before tableName

and after that drop the original one if you dont want it.

Regards

Regards,
Prashant Sangle

Re: Left Join in Partial Reload

I don't think so. A left join creates a new table from two source tables. When you partially reload only the replace load is executed. The other tables remain. That includes the joined Teams table (that contains the color field) from the normal reload. That's also the reason the replace load creates Teams-1 during a partial reload. And since it has all its fields in common with the Teams table you get a synthetic key.


talk is cheap, supply exceeds demand
kevpickering
New Contributor III

Re: Left Join in Partial Reload

Data Model.png

Thanks for getting back.

My apologies, that's a typo. Yes, 2 tables not 3

I'll also rephrase my question.

Is it possible to Replace a table in Partial Reload and then LEFT JOIN to it to add more fields?

kevpickering
New Contributor III

Re: Left Join in Partial Reload

Well, I've managed to achieve it....

The successful script....

IF IsPartialReload() THEN

     DROP TABLE Teams;

END IF;

Teams:

ADD LOAD * INLINE [

     Team, Code

     Wigan, W1

     Dracs, D1

     Rhinos, R2

];

LEFT JOIN (Teams)

ADD LOAD * INLINE [

     Code, Color

     W1, Cherry and white

     D1, Red and Gold

     R2, Blue and Amber

];

Venues:

LOAD * INLINE [

     Code, Venue

     W1, DW Stadium

     D1, Stade de Gilbert

     R2, Headingley

];

Note the explicit dropping of the table in Partial reload, followed by REPLACE ADD LOAD to recreate the Teams table and then LEFT JOIN ADD LOAD to add the extra Color field.

Thanks all for your input.

Community Browser