QV not merging data based on unique key

[#1]

Dear reader,

I have generated a data set to help me enlarge my QV experience.

What I don't understand is why all first rows are empty. I expect that QV merges the records in to unique rows based on the unique column retournr. What am I missing?

The input Excel is attached.

At the end of the table I have a similar issue. I expect every record to have a filled in cell Type.

The QV model is this:

The load script 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);

I hope you can help me I need to deliver the report by the end of this week 😞

John

[swuehl]

Maybe have a look at

Understanding Join, Keep and Concatenate

[#3]

Thanks for answering and willing to help me.

I read all stuff off course on joins keep and concatenate.

I think it is not possible to add data with concatenate and user an inner join.

I need to join the data two avoid double rows. The key item is retournr.

Thinking out loud first do an inner join and then do the concatenate.

John

[patricio]

When you concatenate a table containing 5 rows with another containing 6 rows you will have 11 rows as a result.

May be want you want to do is an outer join instead of a concatenate. I would suggest that you read the document proposed by swuehl because it is very clear and has examples.

If your first table has fields A and B and the second table has fields B and C. Then field C will be null for all rows concatenated from first Table and Field A will be null for all rows concatenated from second table.

[swuehl]

Or maybe concatenate and then do another resident load with a GROUP BY and aggregations?

[marcus_sommer]

A tablebox is very suitable to check the data if they are like you expect them but you need mandatory an unique record-identifier - could be easily created within the script with recno() and/or rowno() - and quite helpful is also to add a 'FromTableXYZ' as Source to identify definitely what your tables contain and from where these data come from.

- Marcus

[sujeetsingh]

check the formatting of the unique key in both temp tables please.

[#8]

See here the changes in the load script I made

Data modeling joining data got 3 rows and expect 1

Which improves the resulted tables.

John