Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I have the following data, please refer the attached excel for complete data. It has the columns Step, StepCode and Date and I need to derive the StepCount column. Based on the Date column I have to increment the count of Step column.
Request your help to achieve this . Please refer the attached Excel. Thank You in Advance.
Step | StepCode | Date | StepCount |
OutSold | B224395 | 12/3/2013 10:46:22 | 1 |
PRC | B224395 | 12/3/2013 10:46:21 | 2 |
B224395 | 12/3/2013 10:28:37 | ||
B224395 | 12/3/2013 10:28:36 | ||
B224395 | 12/3/2013 10:28:19 | ||
B224395 | 12/3/2013 10:28:18 | ||
B224395 | 12/3/2013 10:26:28 | ||
B224395 | 12/3/2013 10:26:27 | ||
B224395 | 12/3/2013 10:11:27 | ||
B224395 | 12/3/2013 10:10:54 | ||
B224395 | 12/3/2013 10:10:52 | ||
B224395 | 12/3/2013 10:10:27 | ||
B224395 | 12/3/2013 10:09:55 | ||
B224395 | 12/3/2013 10:09:54 | ||
B224395 | 11/3/2013 15:36:01 | ||
B224395 | 11/3/2013 15:36:00 | ||
B224395 | 11/3/2013 15:34:27 | ||
B224395 | 11/3/2013 15:34:26 | ||
B224395 | 11/3/2013 15:17:13 | ||
B224395 | 11/3/2013 15:17:12 | ||
B224395 | 11/3/2013 15:17:00 | ||
Transfer | B224395 | 11/3/2013 15:16:59 | 2 |
B224395 | 11/3/2013 14:35:01 | ||
B224395 | 11/3/2013 14:35:00 | ||
B224395 | 11/3/2013 14:27:15 | ||
B224395 | 11/3/2013 14:27:14 | ||
B224395 | 4/3/2013 14:03:17 | ||
B224395 | 4/3/2013 14:03:16 | ||
B224395 | 27/2/2013 18:09:52 | ||
B224395 | 27/2/2013 18:09:51 | ||
B224395 | 27/2/2013 14:05:02 |
Regards,
Alvin
Try
LOAD Step,
StepCode,
Date
FROM
(ooxml, embedded labels, table is Sheet2, filters(
Replace(1, top, StrCnd(null))
));
LOAD *,
If(Previous(Step)=Step,Peek(StepCount), AutoNumber(Recno(),StepCode&Step)) as StepCount
Resident INPUT
ORDER BY StepCode, Date asc;
DROP TABLE INPUT;
Not sure I understand your output? How are you coming up with the StepCount field? What is the logic?
Try
LOAD Step,
StepCode,
Date
FROM
(ooxml, embedded labels, table is Sheet2, filters(
Replace(1, top, StrCnd(null))
));
LOAD *,
If(Previous(Step)=Step,Peek(StepCount), AutoNumber(Recno(),StepCode&Step)) as StepCount
Resident INPUT
ORDER BY StepCode, Date asc;
DROP TABLE INPUT;
Nice trick, the AutoNumber with RecNo, StepCode and Step.
Hi Swuehi,
Thank You for your help .. It works perfectly ...
Cheers !!
Regards,
Alvin.
Please close this thread by marking an answer as correct if your request is resolved.