load first occurance of the row

[ziabobaz]

Hi,

I have a table, which a want to tranform based on the first occurence of the OrderN (see below):

The script:

OrderTMP:

NoConcatenate

LOAD

    ДатаIx,

    OrderN,

     channel

RESIDENT [Main_table]

Where status='order-created'  // status exists in the main table, so i filter out certain records by this field value

order by [ДатаIx] asc

;

OrderTMP:

NoConcatenate

LOAD *

RESIDENT OrderTMP

WHERE NOT EXISTS (OrderN);

DROP FIELD [ДатаIx] FROM OrderTMP;

The result is that both orders 555 exist.

Where is the error?

[sunny_talwar · Accepted solution]

May be try this

OrderTMP:

NoConcatenate

LOAD ДатаIx,

    OrderN,

    channel        

RESIDENT [Main_table]

Where status='order-created'  // status exists in the main table, so i filter out certain records by this field value

order by [ДатаIx] asc;

Right Join (OrderTMP)

LOAD OrderN,

     Min(ДатаIx) as ДатаIx

Resident OrderTMP

Group By OrderN;

[kaanerisen]

How about using FirstSortedValue,

Master:

load

OrderN,

FirstSortedValue(Channel,Date) as Channel

Inline [

"Date","OrderN","Channel"

'01.01.2018',555,'Direct'

'15.02.2018',555,'Refferal'

'06.01.2018',666,'Online'

'14.01.2018',666,'Direct'

'02.02.2018',777,'Referral'

'05.02.2018',777,'Direct'

'03.02.2018',777,'Online'

]

group by OrderN;

[ziabobaz]

wow, this works either

i didn't know about this function

thanks for a clue