topic Re: previous not working correctly in QlikView

previous not working correctly

Fri, 10 May 2013 12:26:27 GMT

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

Re: previous not working correctly

alexandros17 — Fri, 10 May 2013 12:28:37 GMT

Use above, not previous

Re: previous not working correctly

Fri, 10 May 2013 12:32:02 GMT

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

Re: previous not working correctly

swuehl — Fri, 10 May 2013 12:50:58 GMT

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;

Re: previous not working correctly

Sat, 11 May 2013 05:11:17 GMT

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

Re: previous not working correctly

swuehl — Sat, 11 May 2013 10:12:18 GMT

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.

Re: previous not working correctly

Mon, 13 May 2013 05:55:11 GMT

Please help me with this also.

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

Thanks