Merging Rows

[#1]

Hi everyone,

I have a bunch of source tables that I want to concatenate, however Rows will have duplicate IDs. Is there a way to merge the rows and sort of cascade into empty columns based on priority, or even better just assign priority for the columns in general?

For example the concatenation has these rows

ID	A	B	C
12	1
12	2	2
12	3	3	3

And I want to end up with only

ID	A	B	C
12	1	2	3

Is there any way to do this?

[Gysbert_Wassenaar]

LOAD

     ID,

     Min(A) as A,

     Min(B) as B,

     Min(C) as C

FROM

     ....

GROUP BY ID;

[#3]

Sorry the numbers where just an example, the data might be more complex. I am talking about merging them when I want to Concatenate multiple table files, the Rows within each table are unique and I'd want to give the highest priority to the first file and the make the next Concatenate fill up empty fields in rows with IDs that are in both tables.

[petter]

Maybe this could be a solution for you, I have used date as the "priority" but it could be something else:

[#5]

Thank you for the answer Petter. Can you explain how it actually prioritizes the Date? My problem is a bit different as the duplicates are created by loading from multiple files. So should I replicate this with FileBaseName() or is there a way to fill empty fields in the load process.

Say I load my main table with a row like this:

ID 1	ID 2	Datafield1	Datafield2	Datafield3
342	HGJ		112

And then I load a second table

342

HGJ

564

xx

And then a third file with missing primary ID

HGJ

1

1

1

And I want them to merge into this

342

HGJ

564

112

1

Sorry if my question wasn't clear, but can you merge rows and fill up blank fields during LOAD?

[petter · Accepted solution]

1) The priority is determined by the placement of DATE in the Concat()-function:

          Concat( Num( A ) , ' ' , DATE )      this will pick the first (earliest) date ...

          Concat( Num( A ) , ' ' , -DATE )     would pick the latest date ...

          In this context it is important that the dates are in a sortable format like a normal date would be in QlikView.

2) Yes you can fill missing fields as long as you have a well defined and complete logic for it. How easy or hard it is

    to make the logic depends on the complexity of the logic.

    Actually the file import wizard in the Load Script Editor will give you a lot of options to fill missing values. You would

    need to spend some time to get it to work though as it is not super intuitive.

Is your priority set by the load order of each file? So file number 1 would be priority 1 or opposite last priority?

If so it would be possible to have an AutoNumber() that will give you a sequence number for each file if you use

FileName() inside it:

                AutoNumber( FileName() ) AS FileOrder

The you can use FileOrder instead of DATE in the Concat()-functions ...