Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Fill out increments between numbers

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:
NameReceivedReference
a47
a67
a38
a58
b27
b37
b49
b59
b89
What I want the new table to look like (new lines with blue text):
NameReceivedReference
a47
a57
a67
a38
a48
a58
b27
b37
b49
b59
b69
b79
b89
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!
1 Solution

Accepted Solutions
Not applicable

Re: Fill out increments between numbers

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;

 

5 Replies
jonbrough
Valued Contributor

Re: Fill out increments between numbers

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

Re: Fill out increments between numbers

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

Not applicable

Re: Fill out increments between numbers

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.

jonbrough
Valued Contributor

Re: Fill out increments between numbers

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

Not applicable

Re: Fill out increments between numbers

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;

 

Community Browser