Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I need to fill the data, only when it's between the same data.
Key | Date | Field 2 |
---|---|---|
1234 | 01.01.2018 | |
1234 | 02.01.2018 | Data 2 |
1234 | 03.01.2018 | |
1234 | 04.01.2018 | |
1234 | 05.01.2018 | Data 2 |
1234 | 06.01.2018 |
Final Table should be this, in order by Key, Date
Key | Date | Field 2 |
---|---|---|
1234 | 01.01.2018 | |
1234 | 02.01.2018 | Data 2 |
1234 | 03.01.2018 | Data 2 |
1234 | 04.01.2018 | Data 2 |
1234 | 05.01.2018 | Data 2 |
1234 | 06.01.2018 |
With my script everything is filled with the last value.
Can someone help me?
Thanks
Try this
Table:
LOAD * INLINE [
Key, Date, Field 2
1234, 01.01.2018,
1234, 02.01.2018, Data 2
1234, 03.01.2018,
1234, 04.01.2018,
1234, 05.01.2018, Data 2
1234, 06.01.2018,
1234, 07.01.2018,
1234, 08.01.2018, Data 2
1234, 09.01.2018,
1234, 10.01.2018,
1235, 01.01.2018,
1235, 02.01.2018, Data 2
1235, 03.01.2018,
1235, 04.01.2018,
1235, 05.01.2018, Data 2
1235, 06.01.2018,
1235, 07.01.2018,
1235, 08.01.2018, Data 2
1235, 09.01.2018,
1235, 10.01.2018
];
Left Join (Table)
LOAD Key,
Max(Date) as MaxDate,
Min(Date) as MinDate
Resident Table
Where Len(Trim([Field 2])) > 0
Group By Key;
FinalTable:
LOAD Key,
Date,
If(Date >= MinDate and Date <= MaxDate, If(Len(Trim([Field 2])) = 0, Peek('Field 2'), [Field 2])) as [Field 2]
Resident Table
Order By Key, Date;
DROP Table Table;
may be this
LOAD
if(len(trim(Field))=0, peek(Field), Field) as Result, // len(trim(<field>))=0 is the best way to catch NULL and empty string
Value1,
Value2
FROM...
With that statment it fills up everything.
I always have a start and a end, only between that it should fill up the data.
What if you had few more rows like this
Key | Date | Field 2 |
---|---|---|
1234 | 01.01.2018 | |
1234 | 02.01.2018 | Data 2 |
1234 | 03.01.2018 | |
1234 | 04.01.2018 | |
1234 | 05.01.2018 | Data 2 |
1234 | 06.01.2018 | |
1234 | 07.01.2018 | |
1234 | 08.01.2018 | Data 2 |
1234 | 09.01.2018 | |
1234 | 10.01.2018 |
What would be the expected output now?
Key | Date | Field 2 |
---|---|---|
1234 | 01.01.2018 | |
1234 | 02.01.2018 | Data 2 |
1234 | 03.01.2018 | Data 2 |
1234 | 04.01.2018 | Data 2 |
1234 | 05.01.2018 | Data 2 |
1234 | 06.01.2018 | Data 2 |
1234 | 07.01.2018 | Data 2 |
1234 | 08.01.2018 | Data 2 |
1234 | 09.01.2018 | |
1234 | 10.01.2018 |
I always a "start" and "end" of each data, between that it should fill the data.
And this is by Key... for each Key... you would want to use the same data between the start and end date?
This could happen multiple times in 1 Key, but always in order by the key and date.
No, what I meant was what would be the output for this
Key | Date | Field 2 |
---|---|---|
1234 | 01.01.2018 | |
1234 | 02.01.2018 | Data 2 |
1234 | 03.01.2018 | |
1234 | 04.01.2018 | |
1234 | 05.01.2018 | Data 2 |
1234 | 06.01.2018 | |
1234 | 07.01.2018 | |
1234 | 08.01.2018 | Data 2 |
1234 | 09.01.2018 | |
1234 | 10.01.2018 |
1235 | 01.01.2018 | |
1235 | 02.01.2018 | Data 2 |
1235 | 03.01.2018 | |
1235 | 04.01.2018 | |
1235 | 05.01.2018 | Data 2 |
1235 | 06.01.2018 | |
1235 | 07.01.2018 | |
1235 | 08.01.2018 | Data 2 |
1235 | 09.01.2018 | |
1235 | 10.01.2018 |
Oh ok sorry.
Key | Date | Field 2 |
---|---|---|
1234 | 01.01.2018 | |
1234 | 02.01.2018 | Data 2 |
1234 | 03.01.2018 | Data 2 |
1234 | 04.01.2018 | Data 2 |
1234 | 05.01.2018 | Data 2 |
1234 | 06.01.2018 | Data 2 |
1234 | 07.01.2018 | Data 2 |
1234 | 08.01.2018 | Data 2 |
1234 | 09.01.2018 | |
1234 | 10.01.2018 |
1235 | 01.01.2018 | |
1235 | 02.01.2018 | Data 2 |
1235 | 03.01.2018 | Data 2 |
1235 | 04.01.2018 | Data 2 |
1235 | 05.01.2018 | Data 2 |
1235 | 06.01.2018 | Data 2 |
1235 | 07.01.2018 | Data 2 |
1235 | 08.01.2018 | Data 2 |
1235 | 09.01.2018 | |
1235 | 10.01.2018 |
Try this
Table:
LOAD * INLINE [
Key, Date, Field 2
1234, 01.01.2018,
1234, 02.01.2018, Data 2
1234, 03.01.2018,
1234, 04.01.2018,
1234, 05.01.2018, Data 2
1234, 06.01.2018,
1234, 07.01.2018,
1234, 08.01.2018, Data 2
1234, 09.01.2018,
1234, 10.01.2018,
1235, 01.01.2018,
1235, 02.01.2018, Data 2
1235, 03.01.2018,
1235, 04.01.2018,
1235, 05.01.2018, Data 2
1235, 06.01.2018,
1235, 07.01.2018,
1235, 08.01.2018, Data 2
1235, 09.01.2018,
1235, 10.01.2018
];
Left Join (Table)
LOAD Key,
Max(Date) as MaxDate,
Min(Date) as MinDate
Resident Table
Where Len(Trim([Field 2])) > 0
Group By Key;
FinalTable:
LOAD Key,
Date,
If(Date >= MinDate and Date <= MaxDate, If(Len(Trim([Field 2])) = 0, Peek('Field 2'), [Field 2])) as [Field 2]
Resident Table
Order By Key, Date;
DROP Table Table;