Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Use above, not previous
i need to do this script only so above isn't an option
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;
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
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.