Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Please help me to solve this problem.
I need output for below sample example
Date ID
12/01/2016 1
12/01/2016 2
12/01/2016 3
12/02/2016 1
12/02/2016 2
12/03/2016 1
12/04/2016 1
12/04/2016 2
12/04/2016 3
12/04/2016 4
Thanks,
Lawrance. A
Your Category has few incorrect spellings. Considering Marcus script you can use as below:
TEST:
LOAD * INLINE [
Date , Category
1/1/2016, connect
1/1/2016, connect
1/1/2016, connect
1/1/2016, Disconnect
1/1/2016, Disconnect
1/1/2016, Disconnect
1/1/2016, close
1/1/2016, close
2/1/2016, connect
2/1/2016, connect
2/1/2016, connect
2/1/2016, Disconnect
2/1/2016, Disconnect
2/1/2016, Disconnect
2/1/2016, close
2/1/2016, close
];
MISC:
LOAD *, RowNo() AS RowNum,
IF(Date = Previous(Date), Peek(ID)+1, 1) AS ID
Resident TEST
Order By Category,Date;
DROP Table TEST;
May be like this:
LOAD Date,
AutoNumber(RowNo(), Date) as ID
FROM ....
or this:
LOAD Date,
AutoNumber(RowNo(), Floor(Date)) as ID
FROM ....
You could use a to date ordered resident load with:
load
Date,
if(Date = previous(Date), peek('ID') + 1, 1) as ID
resident YourTable order by Date;
- Marcus
Hi,
LOAD
If(Date=Peek(Date), Peek(ID)+1, 1) as ID,
Date
INLINE [
Date
12/01/2016
12/01/2016
12/01/2016
12/02/2016
12/02/2016
12/03/2016
12/04/2016
12/04/2016
12/04/2016
12/04/2016
];
(Perhaps you need to do a order by Date)
Aurélien
Hi,
Please give me solution for this,
I need Sequence number based on the category for selected date.
For Example,
Date ID Category
1/1/2016 1 connect
1/1/2016 2 connect
1/1/2016 3 connect
1/1/2016 1 Disconnect
1/1/2016 2 Disconnect
1/1/2016 3 Dicconnect
1/1/2016 1 close
1/1/2016 2 close
2/1/2016 1 connect
2/1/2016 2 connect
2/1/2016 3 connect
2/1/2016 1 Disconnect
2/1/2016 2 Disconnect
2/1/2016 3 Dicconnect
2/1/2016 1 close
2/1/2016 2 close
Your Category has few incorrect spellings. Considering Marcus script you can use as below:
TEST:
LOAD * INLINE [
Date , Category
1/1/2016, connect
1/1/2016, connect
1/1/2016, connect
1/1/2016, Disconnect
1/1/2016, Disconnect
1/1/2016, Disconnect
1/1/2016, close
1/1/2016, close
2/1/2016, connect
2/1/2016, connect
2/1/2016, connect
2/1/2016, Disconnect
2/1/2016, Disconnect
2/1/2016, Disconnect
2/1/2016, close
2/1/2016, close
];
MISC:
LOAD *, RowNo() AS RowNum,
IF(Date = Previous(Date), Peek(ID)+1, 1) AS ID
Resident TEST
Order By Category,Date;
DROP Table TEST;