Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ziabobaz
Contributor II

load first occurance of the row

Hi,

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

Screenshot_1.jpg

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?

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: load first occurance of the row

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;

3 Replies
MVP
MVP

Re: load first occurance of the row

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

Re: load first occurance of the row

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;

Untitled.png

ziabobaz
Contributor II

Re: load first occurance of the row

wow, this works either

i didn't know about this function

thanks for a clue