Data modeling joining data got 3 rows and expect 1

[#1]

Hi,

After loading I got this result in a table

I expect only 1 row based on the key retournr (=send back number).

I have a load script with test data generated by Excel.

The data load is as follows:

BRS:

LOAD filiaalnr_plain,

     filiaalnr,

     retournr,

     invoerdatum,

     type

FROM

(biff, embedded labels, table is incidenteel$)

where filiaalnr = 'w19';

concatenate(BRS) // inidenteel artikelen

LOAD retournr,

     artikelnr,

     aantal

FROM

(biff, embedded labels, table is incidenteel_art$)

where exists(retournr);

concatenate(BRS) // inidenteel bon

LOAD retournr,

     artikelnr,

     bonnr,

     aantal

FROM

(biff, embedded labels, table is incidenteel_bon$)

where exists(retournr);

concatenate(BRS) // defecten

LOAD 'w' & filiaalnr as filiaalnr,

     retournr,

     invoerdatum,

     type,

     aantal,

     art_nr as artikelnr

FROM

(biff, embedded labels, table is defecten$)

where filiaalnr = 'w19';

concatenate(BRS) //mass

LOAD 'w' & filiaalnr as filiaalnr,

     retournr,

     invoerdatum,

     type,

     aantal,

     art_nr as artikelnr

FROM

(biff, embedded labels, table is mass$)

where filiaalnr = 'w19';

org_fil:

LOAD 'w' & filiaalnr_plain as filiaalnr,

     type as filiaaltype

FROM

(biff, embedded labels, table is org_stam$)

where exists ('w' & filiaalnr_plain);

org_art:

LOAD art_nr as artikelnr,

     omschrijving

FROM

(biff, embedded labels, table is org_art$)

where exists (art_nr);

So only shop (=filiaal in Dutch) w19 is loaded but in the table I got 3 rows. I expected only 1 row.

The data model is like this:

Your help will be appreciated.

John

[swuehl]

And why do you expect only 1 row?

I mean, which WHERE condition should prevent the records to be appended / concatenated?

All rows seem to share the same retournr, so WHERE exists() should be true for these rows.

[#3]

Thanks for your answer.

Why do I expect only 1 row? I thought that Qlikview will merge the data based on the same key "RetourNr". Now it looks like an outer join only.

Is it possible to concantenate and still do a join. I couldn't find an example for this.

John

[swuehl]

Not really sure what you are after.

Maybe do another resident load of your table with a GROUP by and aggregation functions like Only().

[#5]

I have changed the load script as follows with better results. Apparently Qlikview is only adding rows and doesn't use the key columns for a join.

If you specify inner join and outer join you got the result I was looking for.

BRS:

LOAD filiaalnr_plain,

     filiaalnr,

     retournr,

     invoerdatum,

     type

FROM

(biff, embedded labels, table is incidenteel$)

;

//where filiaalnr = 'w19';

//concatenate(BRS) // inidenteel artikelen

incidenteel_art:

inner join (BRS)

LOAD retournr,

     artikelnr,

     aantal

FROM

(biff, embedded labels, table is incidenteel_art$)

where exists(retournr);

inner join (BRS) // inidenteel bon

LOAD retournr,

     artikelnr,

     bonnr,

     aantal

FROM

(biff, embedded labels, table is incidenteel_bon$)

where exists(retournr);

//concatenate(BRS) // defecten

defects:

outer join (BRS)

LOAD 'w' & filiaalnr as filiaalnr,

     retournr,

     invoerdatum,

     type,

     aantal,

     art_nr as artikelnr

FROM

(biff, embedded labels, table is defecten$)

;

//where filiaalnr = 'w19';

mass:

outer join(BRS) //mass

LOAD 'w' & filiaalnr as filiaalnr,

     retournr,

     invoerdatum,

     type,

     aantal,

     art_nr as artikelnr

FROM

(biff, embedded labels, table is mass$)

;

//where filiaalnr = 'w19';

org_fil:

LOAD 'w' & filiaalnr_plain as filiaalnr,

     type as filiaaltype

FROM

(biff, embedded labels, table is org_stam$)

where exists ('w' & filiaalnr_plain);

org_art:

LOAD art_nr as artikelnr,

     omschrijving

FROM

(biff, embedded labels, table is org_art$)

where exists (art_nr);

John

[#6]

Hi,

Great work !...may be just small tweak required like while using "inner join" no need to use "where exists(retournr)" .