Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cpalbrecht
Contributor

Merge rows based on specific conditions

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

I have the following data table:

DateValue1Value2
DateAaV1
DateAa-
DateBb-
DateBbV2
DateCcV3
DateDd-
DateEe-
DateFfV4
DateFf-

And I want to have a table like this:

DateValue1Value2
DateAaV1
DateBbV2
DateCcV3
DateDd-
DateEe-
DateFfV4

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

1 Solution

Accepted Solutions
niclaz79
Contributor III

Re: Merge rows based on specific conditions

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;

7 Replies
niclaz79
Contributor III

Re: Merge rows based on specific conditions

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
Contributor II

Re: Merge rows based on specific conditions

May be this at value 2 dimension add

=  Aggr(Value2, Date)

cpalbrecht
Contributor

Re: Merge rows based on specific conditions

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

cpalbrecht
Contributor

Re: Merge rows based on specific conditions

@ 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:

DateValue1Value2
DateAaV11
DateAaV12
DateBb-
DateBbV2
DateCcV3
DateDd-
DateEe-
DateFfV4
DateFf-

And I want to have a table like this:

DateValue1Value2
DateAaV11
DateBbV2
DateCcV3
DateDd-
DateEe-
DateFfV4
niclaz79
Contributor III

Re: Merge rows based on specific conditions

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;

MVP
MVP

Re: Merge rows based on specific conditions

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
Valued Contributor

Re: Merge rows based on specific conditions

looks like you have to load district on value1

or you want to do some other way?

Community Browser