Fill out increments between numbers

[#1]

Hi,

I wish to generate a table which fills out integers, based on another table. I want all the integers between Min and max of received (with the same Name and Reference) to be generated.

(Edit - added for clarity):

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.

Example of first table:

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

What I want the new table to look like (new lines with blue text):

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

Inline script:

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
]; 

Any help is much appreciated!

[Anonymous]

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

[hic]

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

[#4]

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.

[Anonymous]

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

[#6 · Accepted solution]

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;