Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;
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;
wow, this works either
i didn't know about this function
thanks for a clue