topic Re: a Previous() issue in QlikView

a Previous() issue

Anonymous — Fri, 14 Nov 2014 10:11:15 GMT

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.

Re: a Previous() issue

Gysbert_Wassenaar — Fri, 14 Nov 2014 10:32:56 GMT

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;

Re: a Previous() issue

marcus_malinow — Fri, 14 Nov 2014 10:34:55 GMT

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

Re: a Previous() issue

Anonymous — Fri, 14 Nov 2014 10:42:48 GMT

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

Marc.

Re: a Previous() issue

Anonymous — Fri, 14 Nov 2014 10:43:55 GMT

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.

Re: a Previous() issue

Gysbert_Wassenaar — Fri, 14 Nov 2014 10:45:00 GMT

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

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

Re: a Previous() issue

Anonymous — Fri, 14 Nov 2014 11:08:00 GMT

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.

Re: a Previous() issue

Gysbert_Wassenaar — Fri, 14 Nov 2014 11:09:13 GMT

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