topic Re: QV not merging data based on unique key in QlikView

QV not merging data based on unique key

Wed, 27 Apr 2016 13:53:11 GMT

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

Re: QV not merging data based on unique key

swuehl — Wed, 27 Apr 2016 16:26:13 GMT

Maybe have a look at

Understanding Join, Keep and Concatenate

Re: QV not merging data based on unique key

Wed, 27 Apr 2016 17:59:06 GMT

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

Re: QV not merging data based on unique key

patricio — Wed, 27 Apr 2016 19:33:17 GMT

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.

Re: QV not merging data based on unique key

swuehl — Wed, 27 Apr 2016 21:17:13 GMT

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

Re: QV not merging data based on unique key

marcus_sommer — Thu, 28 Apr 2016 05:34:57 GMT

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

Re: QV not merging data based on unique key

sujeetsingh — Thu, 28 Apr 2016 05:41:55 GMT

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

Re: QV not merging data based on unique key

Thu, 28 Apr 2016 06:12:15 GMT

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