Using peek function

[Inna]

Hi ,

I have following question.

i have following script:

FinalFactDeals:
LOAD
distinct DealId,
//peek( DealId,0) as DealId,
[Deal Name],
Company,
AE,
ActivityDateMonth,
[Activity Year-Month],
CloseDate,
DealAmount,
Acc_Deal_Amount,
DiscountDeal,
AvgRevenueForCommission,
ReturnCommisionForChurnIndication,
FirstPartCommission as CommissionAmount,
'Commission' as ReasonForNoCommission,
'1st Part' as CommissionType,
"Market (Role)",
"Sales Team"
FROM [lib://ProcessQvd-Commissions (win-kb7km7dcp2l_qvadmin)/FactWithDiscount.qvd](qvd)
Where RevenueYearMonth=FirstDealYearMonth;

Concatenate
LOAD
distinct DealId,
//peek( DealId,0) as DealId,
[Deal Name],
Company,
AE,
TimeSecondCommissionYearMonth as ActivityDateMonth,
[Activity Year-Month Second] as [Activity Year-Month],
CloseDate,
DealAmount,
Acc_Deal_Amount,
DiscountDeal,
AvgRevenueForCommission,
ReturnCommisionForChurnIndication,
FirstPartCommission,
OriginalSecondCommission,
SecondPartCommission as CommissionAmount,
//ChurnDate,
if(NoCommissionTermination=1,'AE Termination',
if(NoThreeMonths=1,'3 Months for Second Commission did not pass yet',if(ReturnCommisionForChurnIndication=1,'Churn','Commission'))) as ReasonForNoCommission,
'2nd Part' as CommissionType,
"Market (Role)",
"Sales Team",
DealId &'2nd Part' as Deal_Type_Key
FROM [lib://ProcessQvd-Commissions (win-kb7km7dcp2l_qvadmin)/FactWithDiscount.qvd](qvd)
Where RevenueYearMonth=TimeSecondCommissionYearMonth;

I need to exchange 'distinct' function wiht peek function. However while using 'peek( DealId,0) as DealId' instead of 'distinct DealId' I get duplicate rows. Please advise what function I should use. Thanks, Inna.

[Inna]

Hi,

can anybody answer?

[rubenmarin]

Hi, it's not clear what you want to get using Peek(), It returns the value loaded in the previuos row, so why you want to assign to DealId the value of the previous DealId?.

If you only want to load one row for each DealId you can use the Exist() function on the where clause:

Where RevenueYearMonth=TimeSecondCommissionYearMonth
  and not Exists(DealId)
;