topic Re: Overwrite fields in existing rows in a table in QlikView

Overwrite fields in existing rows in a table

Wed, 12 Dec 2012 22:13:17 GMT

Hi all. So I have a data table that is an aggregate of several similar data sets. Let's say I have 5 spreadsheets, each from a different month. Each spreadsheet has a numeric score tied to a record ID. I basically want to load an aggregate table that contains all record IDs, but only the max numeric value across all spreadsheets.

So say my data sets are like this:

Jan:

LOAD * Inline

[Record, Score

A, 1

B, 2];

Feb:

LOAD * Inline

[Record, Score

A, 2

B, 3];

Mar:

LOAD * Inline

[Record, Score

A, 0

B, 5];

I want the final result set to look like this:

Final:

[Record, Score

A, 2

B, 5];

Any ideas? Thanks!

Eric

Re: Overwrite fields in existing rows in a table

MayilVahanan — Wed, 12 Dec 2012 22:26:17 GMT

Try like this

Jan:

LOAD * Inline

[Record, Score

A, 1

B, 2];

Join(Jan)

LOAD * Inline

[Record, Score

A, 2

B, 3];

Join(Jan)

LOAD * Inline

[Record, Score

A, 0

B, 5];

Table1:

Load Max(Score) as MaxScore,Record Resident Jan Group by Record;

Re: Overwrite fields in existing rows in a table

Thu, 13 Dec 2012 16:56:53 GMT

Hey thanks for the reply. So, I see what you're saying, but don't you mean to use concatenate instead of join? I would think that if you used join, it wouldn't load anything because QlikView would look at the record and score field, not find a matching record, and go on to the next one. With concatenate, it would create multiple rows and then at the end take the max. Does that sound right?

Anyway I'll give it a shot. Thanks!

Eric