previous not working correctly

[#1]

Hi,

Attached an excel sheet i need to have the Date column show one date less if:

if(Product=Previous(Product) and ColumnFrom<Previous(ColumnFrom) and Date=Previous(Date),date(Date-1),Date) as Date

order by Product,ColumnFrom desc;

See bold 15/08/2013--> 14/08/2013  below for Product 0542

i think the requirement is best decribed by the expression above, but it is not working correctly for some products

0542	MZPWDZXT  OIX - KETRZXZFEXD	30/09/2013	31
0542	MZPWDZXT  OIX - KETRZXZFEXD	15/08/2013	31
0542	GT, Hrfizylcgtqf, Oqsfdkeh	14/08/2013	11
0542	GT, Hrfizylcgtqf, Oqsfdkeh	31/05/2013	11
0542	GT, Hrfizylcgtqf, Oqsfdkeh	18/03/2013	11
0542	CG, Wyyo, Myypnps	17/03/2013	1
0542	CG, Wyyo, Myypnps	07/03/2013	1
0542	CG, Wyyo, Myypnps	20/12/2012	1

Regards

[alexandros17]

Use above, not previous

[#3]

i need to do this script only so above isn't an option

[swuehl · Accepted solution]

Just sorting by Product and ColumnFrom allows QV to sort the records different from what you might need, I assume you need to keep to your original input sort order ordered additionally by Date desc.

Something like:

SET DateFormat = 'DD/MM/YYYY';

INPUT:

LOAD Product,

     Category,

     Date,

     ColumnFrom,

     recno() as RecID

FROM

[Ques date-1.xls]

(biff, embedded labels, table is Sheet1$);

RESULT:

LOAD RecID as Rowno,

    Product,

    Category,

    if(Product=Previous(Product) and ColumnFrom<Previous(ColumnFrom) and Date=Previous(Date),date(Date-1),Date) as Date,

    ColumnFrom

RESIDENT INPUT

order by Product,ColumnFrom desc, Date desc;

DROP TABLE INPUT;

[#5]

Thanks it worked perfectly.

But i need to know:

1) the role of RecID here

2) "Just sorting by Product and ColumnFrom allows QV to sort the records different from what you might need"

what is wrong with this"?

order by Product,ColumnFrom desc, Date desc;

why not order by Product,ColumnFrom,Date desc;

Thanks

[swuehl]

1) You can drop the RecID, you can use it of you want to sort by the previous input record of your excel table instead by Date.

If you remove the RecID / Rowno lines in the load, take care to use a NOCONCATENATE prefix with the second LOAD to avoid auto-concatenating the two tables:

SET DateFormat = 'DD/MM/YYYY';

INPUT:

LOAD Product,

     Category,

     Date,

     ColumnFrom

FROM

[Ques date-1.xls]

(biff, embedded labels, table is Sheet1$);

RESULT:

NOCONCATENATE LOAD,

    Product,

    Category,

    if(Product=Previous(Product) and ColumnFrom<Previous(ColumnFrom) and Date=Previous(Date),date(Date-1),Date) as Date,

    ColumnFrom

RESIDENT INPUT

order by Product,ColumnFrom desc, Date desc;

DROP TABLE INPUT;

2) why not order by ColumnFrom ascending (ascending is the default sort order)?

Well, that was your initial requirement, check your first post.

[#7]

Please help me with this also.

http://community.qlik.com/thread/81911

Thanks