Merge rows based on specific conditions

[cpalbrecht]

I want to load the following table within the data load script.

I have the following data table:

Date	Value1	Value2
DateA	a	V1
DateA	a	-
DateB	b	-
DateB	b	V2
DateC	c	V3
DateD	d	-
DateE	e	-
DateF	f	V4
DateF	f	-

And I want to have a table like this:

Date	Value1	Value2
DateA	a	V1
DateB	b	V2
DateC	c	V3
DateD	d	-
DateE	e	-
DateF	f	V4

If two rows are identical except one field, I want to keep only the row which is not null. If there is only one row, then then keep the row with or without a null value.

How would you do that in Qlik Sense data load editor?

BR

[niclaz79 · Accepted solution]

Hi,

This worked for me:

Temp:

LOAD * Inline [

Date, Value1, Value2

DateA, a, V1

DateA, a,

DateB, b,

DateB, b,  V2

DateC, c, V3

DateD, d,

DateE, e,

DateF, f, V4

DateF, f,

];

Temp2:

NoConcatenate

LOAD Distinct

Date,

Value1

Resident Temp;

Temp3:

NoConcatenate

LOAD Distinct

Date,

Value2

Resident Temp;

drop table Temp;

Left join(Temp2)

LOAD

*

Resident Temp3

where not Value2 = '';

drop table Temp3;

[bhavvibudagam]

May be this at value 2 dimension add

=  Aggr(Value2, Date)

[cpalbrecht]

Sorry, I did not mention that I want to load the data with the data load script.

[cpalbrecht]

@ niclaz79 Thanks for your answer!

How would you do it, if you don't know the second value and it doesn't matter which row do you keep? But you still want only one row of two duplicates based on the other fields:

Date	Value1	Value2
DateA	a	V11
DateA	a	V12
DateB	b	-
DateB	b	V2
DateC	c	V3
DateD	d	-
DateE	e	-
DateF	f	V4
DateF	f	-

And I want to have a table like this:

Date	Value1	Value2
DateA	a	V11
DateB	b	V2
DateC	c	V3
DateD	d	-
DateE	e	-
DateF	f	V4

[niclaz79]

In that case this works:

Temp:

LOAD * Inline [

Date, Value1, Value2

DateA, a, V11

DateA, a, V12

DateA, a,

DateB, b,

DateB, b,  V2

DateC, c, V3

DateD, d,

DateE, e,

DateF, f, V4

DateF, f,

];

Temp2:

NoConcatenate

LOAD Distinct

Date,

Value1

Resident Temp;

Temp3:

Mapping LOAD

Distinct

Date & '|' & Value1 as Key,

Value2

Resident Temp where Value2 <> '';

drop table Temp;

Temp4:

NoConcatenate

LOAD

*,

ApplyMap('Temp3',Date & '|' & Value1,null()) as Value2

Resident Temp2;

drop table Temp2;

[maxgro]

How would you do it, if you don't know the second value and it doesn't matter which row do you keep? But you still want only one row of two duplicates based on the other fields:

you can order the records by date and keep the first (by date)

in the order by you can choose the first one sorting by date and value2

t:

LOAD Date,

     Value1,

     Value2

FROM

[https://community.qlik.com/thread/295298]

(html, codepage is 1252, embedded labels, table is @1);

f:

NoConcatenate LOAD

*

Resident t

where Date <> Peek('Date')

order by Date, Value2 desc;

DROP Table t;

[zebhashmi]

looks like you have to load district on value1

or you want to do some other way?