topic Re: Data modeling joining data got 3 rows and expect 1 in QlikView

Data modeling joining data got 3 rows and expect 1

Wed, 27 Apr 2016 12:39:07 GMT

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

Re: Data modeling joining data got 3 rows and expect 1

swuehl — Wed, 27 Apr 2016 16:12:51 GMT

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.

Re: Data modeling joining data got 3 rows and expect 1

Wed, 27 Apr 2016 17:51:42 GMT

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

Re: Data modeling joining data got 3 rows and expect 1

swuehl — Wed, 27 Apr 2016 21:21:18 GMT

Not really sure what you are after.

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

Re: Data modeling joining data got 3 rows and expect 1

Thu, 28 Apr 2016 06:08:59 GMT

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

Re: Data modeling joining data got 3 rows and expect 1

Thu, 28 Apr 2016 07:32:58 GMT

Hi,

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