a Previous() issue

[Anonymous]

Hi all,

I think something is wrong in my load sentence... I have a table with a dimension and a date, where each dimension can have multiple dates values. I want to get the order of the dates in each dimension value. I tryed with this, but I get an non existing field error:

OrderedTable: NoConcatenate

LOAD *, if(RowNo()=1, 1, if(DimensionField=Previous(DimensionField), Previous(OrderNum)+1, 1)) as OrderNum

Resident NotOrderedTable Order By DimensionField ASC, DateField ASC;

DROP Table NotOrderedTable;

Expected output:

DimensionField     DateField      OrderNum

A                         01/01/2014     1

A                         05/06/2014     2

B                         02/04/2014     1

B                         03/06/2014     2

B                         12/08/2014     3

C                         01/01/2013     1

PFA. Could you please help me to fix it, or the get it by another way?

Thanks in advance

Marc.

[Gysbert_Wassenaar · Accepted solution]

You're creating the OrderNum field. It does not exist in your source data. That's why you can't use the previous() function. You'll have to use peek instead. But you can also do it like this:

LOAD

     DimensionField,

     DateField,

     autonumber(DateField, DimensionField) as OrderNum

Resident NotOrderedTable

Order By DimensionField, DateField;

[marcus_malinow]

if(isnull(Peek('DimensionField',-1), 1, if(DimensionField=Peek('DimensionField',-1), peek('OrderNum',-1) + 1, 1) as OrderNum

[Anonymous]

Thanks Gysbert. I'll try with Peek. Autonumber returns an unique field value, and that's not the point.

Marc.

[Anonymous]

Marcus,

2x) missing but when fixed it works:

if(isnull(Peek('DimensionField',-1)), 1, if(DimensionField=Peek('DimensionField',-1), peek('OrderNum',-1) + 1, 1)) as OrderNum

Thanks.

[Gysbert_Wassenaar]

Autonumber returns an unique field value, and that's not the point.

Try it first, to make sure you're not missing a point .

[Anonymous]

Sorry Gysbert, you're right. I thought I was not explaining my trouble correctly. I never used Autonumber function for this purpose and it's the best solution here.

Thank you.

Marc.

[Gysbert_Wassenaar]

No problem. Glad I was able to show you a new trick.