Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, everyone.
I have the table:
Data:
LOAD * Inline [
DATE, ID, COUNT
01.01.2016, 1001, 2
01.01.2016, 1002, 0
01.01.2016, 1003, 1
01.01.2016, 1004, 0
02.01.2016, 1001, 0
02.01.2016, 1002, 0
02.01.2016, 1003, 0
02.01.2016, 1004, 3
03.01.2016, 1001, 5
03.01.2016, 1002, 0
03.01.2016, 1003, 0
03.01.2016, 1004, 0
04.01.2016, 1001, 0
04.01.2016, 1002, 0
04.01.2016, 1003, 0
04.01.2016, 1004, 1
];
I need to propagate data in column COUNT.
For example. If in current day (02.01.2016) the COUNT equal 0 i need to preview COUNT when wasn't 0.
I need to will get the next table:
Data:
LOAD * Inline [
DATE, ID, COUNT
01.01.2016, 1001, 2
01.01.2016, 1002, 0
01.01.2016, 1003, 1
01.01.2016, 1004, 0
02.01.2016, 1001, 2
02.01.2016, 1002, 0
02.01.2016, 1003, 1
02.01.2016, 1004, 3
03.01.2016, 1001, 5
03.01.2016, 1002, 0
03.01.2016, 1003, 1
03.01.2016, 1004, 3
04.01.2016, 1001, 5
04.01.2016, 1002, 0
04.01.2016, 1003, 1
04.01.2016, 1004, 1
];
I don't know how will do it.
P. s. I am sorry for my english.
Try this:
Data:
LOAD * Inline [
DATE, ID, COUNT
01.01.2016, 1001, 2
01.01.2016, 1002, 0
01.01.2016, 1003, 1
01.01.2016, 1004, 0
02.01.2016, 1001, 0
02.01.2016, 1002, 0
02.01.2016, 1003, 0
02.01.2016, 1004, 3
03.01.2016, 1001, 5
03.01.2016, 1002, 0
03.01.2016, 1003, 0
03.01.2016, 1004, 0
04.01.2016, 1001, 0
04.01.2016, 1002, 0
04.01.2016, 1003, 0
04.01.2016, 1004, 1
];
FinalTable:
NoConcatenate
LOAD DATE,
ID,
If(Peek('ID') = ID and COUNT = 0, Alt(Peek('COUNT'), COUNT), COUNT) as COUNT
Resident Data
Order By ID, DATE;
DROP Table Data;
Try this:
Data:
LOAD * Inline [
DATE, ID, COUNT
01.01.2016, 1001, 2
01.01.2016, 1002, 0
01.01.2016, 1003, 1
01.01.2016, 1004, 0
02.01.2016, 1001, 0
02.01.2016, 1002, 0
02.01.2016, 1003, 0
02.01.2016, 1004, 3
03.01.2016, 1001, 5
03.01.2016, 1002, 0
03.01.2016, 1003, 0
03.01.2016, 1004, 0
04.01.2016, 1001, 0
04.01.2016, 1002, 0
04.01.2016, 1003, 0
04.01.2016, 1004, 1
];
FinalTable:
NoConcatenate
LOAD DATE,
ID,
If(Peek('ID') = ID and COUNT = 0, Alt(Peek('COUNT'), COUNT), COUNT) as COUNT
Resident Data
Order By ID, DATE;
DROP Table Data;
Hi, Sunny T
Thanks a lot.
I am sorry for long answer.
Perfect method. I will try transfer it on full data and will give you the answer.
I think it will work.
I will write to your later.
Hi, Sunny T again
Your method work perfect.
You are really professional,
Thanks a lot.