Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
The rule could be formulated as following:
For each Name and Reference:
Find its smallest and largest "Received" value.
Fill out all integers between these values in the Received column.
Name | Received | Reference |
---|---|---|
a | 4 | 7 |
a | 6 | 7 |
a | 3 | 8 |
a | 5 | 8 |
b | 2 | 7 |
b | 3 | 7 |
b | 4 | 9 |
b | 5 | 9 |
b | 8 | 9 |
Name | Received | Reference |
---|---|---|
a | 4 | 7 |
a | 5 | 7 |
a | 6 | 7 |
a | 3 | 8 |
a | 4 | 8 |
a | 5 | 8 |
b | 2 | 7 |
b | 3 | 7 |
b | 4 | 9 |
b | 5 | 9 |
b | 6 | 9 |
b | 7 | 9 |
b | 8 | 9 |
LOAD* INLINE [
Name, Received, Reference
a, 4, 7
a, 6, 7
a, 3, 8
a, 5, 8
b, 2, 7
b, 3, 7
b, 4, 9
b, 5, 9
b, 8, 9
];
Thanks a lot!
I didn't know you could use the While function in such way!
In case any one else needs it, the final, working script looks like this:
OrigTab:
LOAD
* INLINE [
Name, Received, Reference
a, 4, 7
a, 6, 7
a, 3, 8
a, 5, 8
b, 2, 7
b, 3, 7
b, 4, 9
b, 5, 9
b, 8, 9
];
TempTab:
LOAD
Name,
Max(Received) As MaxReceived,
Min(Received) As MinReceived,
Reference
Resident OrigTab
Group by Name, Reference;
DROP TABLE OrigTab;
TempTab2:
LOAD
Name,
MinReceived + IterNo() - 1 As Received,
Reference
Resident TempTab
While IterNo() <= MaxReceived - MinReceived + 1 ;
DROP TABLE TempTab;
I've not quite understood what rules you are using for setting the Refernce on these new records, but you could:
1. use a cartesian join to multipy the records to return all combinations of fields Name and Received
2. join on your reference field.
3. join on the min and max of your Received field
4. recreate your table with Received between the min and max
Something like this:
OrigTab:
LOAD
* INLINE [
Name, Received, Reference
a, 4, 7
a, 6, 7
a, 3, 8
a, 5, 8
b, 2, 7
b, 3, 7
b, 4, 9
b, 5, 9
b, 8, 9
];
// 1. use a cartesian join to multipy the records to return all combinations of fields Name and Received
NewTab:
LOAD Name
RESIDENT OrigTab;
LEFT JOIN (NewTab)
LOAD Received
RESIDENT OrigTab;
// 2. join on your reference field, grouping bu Name and Received
LEFT JOIN (TempTab)
LOAD Name,
Received,
only(Reference) AS Reference
RESIDENT OrigTab
GROUP BY Name, Received;
// 3. join on the min and max of your Received field
LEFT JOIN (TempTab)
LOAD Name,
min(Received) AS MinReceived,
max(Received) AS MaxReceived
RESIDENT OrigTab
GROUP BY Name;
// 4. recreate your table with Received between the min and max
NewTab:
NOCONCATENATE LOAD
Name,
Received,
Reference
RESIDENT TempTab
WHERE Received >= MinReceived
AND Received <= MaxReceived;
DROP TABLES OrigTab, TempTab;
Jonathan
Two methods to do this are described in the following blog posts:
How to populate a sparsely populated field
Creating Reference Dates for Intervals
HIC
Hi
Sorry if I was unclear. The rule could be formulated as following:
For each Name and Reference:
Find its smallest and largest "Received" value.
Fill out all integers between these values in the Received column.
Unfortunately it doesn't seem like your script does this.. The row [b, 7, 9] in my example above is missing for example.
True. Go for Henric's filling out of reference data blogs. These seem to act in the same way as building a master calednar does.
Jonathan
Thanks a lot!
I didn't know you could use the While function in such way!
In case any one else needs it, the final, working script looks like this:
OrigTab:
LOAD
* INLINE [
Name, Received, Reference
a, 4, 7
a, 6, 7
a, 3, 8
a, 5, 8
b, 2, 7
b, 3, 7
b, 4, 9
b, 5, 9
b, 8, 9
];
TempTab:
LOAD
Name,
Max(Received) As MaxReceived,
Min(Received) As MinReceived,
Reference
Resident OrigTab
Group by Name, Reference;
DROP TABLE OrigTab;
TempTab2:
LOAD
Name,
MinReceived + IterNo() - 1 As Received,
Reference
Resident TempTab
While IterNo() <= MaxReceived - MinReceived + 1 ;
DROP TABLE TempTab;