How can I design a Qlik sheet table so it always displays exactly 1 million rows?

[SSark]

I have a Qlik sheet with a table that shows rows in fixed ranges (for example: 1–999,999; 1,000,000–1,999,999; etc.). When a Row Range is selected from a filter pane, the table displays only rows from that range.

The table includes fields such as Origination and Details_ID, and there is a filter pane for Organization (values including -> Aviation, HQ, Commercial, SuperSonic, etc.).

Upon selecting Aviation in the Origination filter, the table shows only Aviation rows within the chosen Row Range — it does not pull rows outside that range.

For example, if I select the range 1,000,000–1,999,999 but only 50,000 Aviation rows exist in that range, the table will show those 50,000 rows (not the full 1,000,000).

If there are 1,000,000 Aviation rows in the range, it will show 1 million. The table must always display 1,000,000 rows.

So when a selected filter returns fewer rows (say 75,000 Aviation rows), the table will show those 75,000 rows plus additional rows with Organization = '-' .  So, additional rows must be added after 75,000 rows being added first in the table and make it total 1 million rows, and the row numbers remain sequential (1, 2, 3, …).

Please explain step by step how I can implement this — I’m new to Qlik, so a simple example and clear explanation would be very helpful. 

For test purpose we can start with 5000 or 10,000 rows. 

I have coded this :

ElmDetails: 

LOAD *, 

 RecNo() AS RowNum, 

 // helper for alignment 

 'Range ' & 

 Num(Floor((RecNo()-1)/1000000) * 1000000 + 1, '#,##0') & ' - ' & 

 Num(Floor( ((RecNo()-1)/1000000) * 1000000) + 999999, '#,##0') AS RowRange 

RESIDENT ElmExpenses_Data

Also in the sheet I created vStartRange and vEndRange variable to pick up the range:

Ex: if I select RowRange 1,000,000 - 1,999,999 then vStartRange is 1000000 and vEndRange is 1999999 

[marcus_sommer]

Essential is the understanding that an UI table could only display existing records. This means the missing rows to 1 M in regard to the selection state must be created.

Principally such measurement could be done within the UI with calculated dimensions or with appropriate populated records within the data-model - whereby the last should be the preferred method because calculated dimensions are tricky, having limitations in the usability and mostly disadvantages in regard to the performance. Thinkable is something with a valueloop(count(CurrentRows), 1000000, 1) ... 

Therefore it might be better to create all records within the script, maybe with something like:

concatenate(MyFact)

load *, iterno() as RowNum, 'populated' as Source while iterno() <= 1000000;
load pick(recno(), 'Aviation', 'HQ', 'Commercial', 'SuperSonic') as Organization
autogenerate 4;

and then an expression in the way of:

alt(sum({< Source -= {'populated'}>} MyField),
   sum({< RowNum = {">$(=count(CurrentRows))"}, Source = {'populated'}>} 0))

may combine the native data with the populated ones.

Depending on your real scenario the needed efforts may become quite expensive - maybe more as be sensible for something which is only nice to have ...

[SSark]

Hi thank you for your response, and I appreciate it very much. 

I am not sure I am following you correctly. This could be because I am a newbie to Qlik Sense. 

I have this code 

// 1. Load original table with all fields

RawData:

LOAD

    RowNo() AS RowNo,

    *

Resident Expense;

// 2. Generate scaffold RowIDs for your range

RowScaffold:

LOAD

    *,

    'Range ' &

        Num(Floor((RowNo()-1)/1000000) * 1000000 + 1, '#,##0') &

        ' - ' &

        Num(Floor((RowNo()-1)/1000000) * 1000000 + 999999, '#,##0') AS RowRange

RESIDENT RawData;

// 3. Join scaffold with original data

LEFT JOIN (RowScaffold)

LOAD

    RowNo,

    CUSTOMER_ID,

    Organization

RESIDENT RawData;

// 4. Apply rules:

//    - Organization = 'No Data' if not selected

//    - Other fields = value if exists, else '....'

FinalTable:

LOAD

    RowNo,

    RowRange,

    IF(Len(Trim(CUSTOMER_ID))=0, '....', CUSTOMER_ID) AS CUSTOMER_ID,

    IF(Len (Trim(Organization))=0, 'No Data', Organization) AS Organization

RESIDENT RowScaffold;

// 5. Cleanup

DROP TABLE RowScaffold;

_______________________________________________________________________________

Then in the Qlik Seet, I have two filter and a details table. A range and an organization filter, Let's say I select Range 1,000,000 - 1,999,999 and Organization 'Aviation', then Details Table expected display ex: 

RowID	RowRange	Organization	Customer_ID
1000000	1000000 - 1999999	Aviation	D001
1000001	1000000 - 1999999	No Data	D002
1000002	1000000 - 1999999	Aviation	D003
1001205	1000000 - 1999999	No Data	D004
1400002	1000000 - 1999999	Aviation	D005
1500600	1000000 - 1999999	No Data	D006
..	1000000 - 1999999	No Data	D007
..	1000000 - 1999999	Aviation	D008
..	1000000 - 1999999	No Data	D009
1999999	1000000 - 1999999	Aviation	D010

In the Details table in Organization field, this is the expression:

 =If(Organization=GetFieldSelections(Organization), 'No Data', Organization).

This code only working like this.

•  Selected a range and details table showing all rows sequentially starting from 1000,000 till 1999,999 and Organization field as well as other field data. 
• Selected a range and Organization a filter "Aviation' and details table showing data but this way 

RowID	RowRange	Organization	Customer_ID
1000100	1000000 - 1999999	Aviation	100020
1000101	1000000 - 1999999	Aviation	121215
1000200	1000000 - 1999999	Aviation	169918
1100120	1000000 - 1999999	Aviation	186745

• So, lots of rows are missing in between, instead of showing those Rows sequentially with Row ID and Organization 'No Data' etc. 

Your assistance to solve this will be monumental, Thank you very much. 

[marcus_sommer]

A join isn't suitable for a scenario which intends to populate data because a join creates only extra records if it's applied in a cartesian way (the above shown snippet to create a combination of n records per organization might be performed with it instead of the while-loop) or if the relationship of the tables isn't 1:n else n:m. Respectively not for the final task.

Let's assume you have 10 organizations with 10 M of records. The average would be 1 M per organization but in the reality some will have more and some lesser records. My suggested approach creates now 1 M of (dummy) records for each organization and adds them to the facts. That's logically and technically quite simple and ensured that each organization has at least 1 M of real records.

That there are then 20 M of records instead of 10 M within the data-model isn't a challenge because Qlik is using a column-level storage and not a row-level storage like classical SQL tools which means that the impact of the sizing is very small.

Then remains only the part to combine both parts - native and populated data - within the right order within the chart whereby I could imagine that the hinted alt() should do the job.

[SSark]

Thank you very much.

So if I understand correctly,

Would you say this is the correct approach:

// 1. Create a master scaffold table with all 1,000,000 RowIDs and Ranges.
// This is the foundation that guarantees a fixed number of rows.
// ==============================================================================
RowScaffold:
LOAD
    RowNo() AS RowID,
    'Range ' &
        Num(Floor((RowNo()-1)/1000000) * 1000000 + 1, '#,##0') &
        ' - ' &
        Num(Floor((RowNo()-1)/1000000) * 1000000 + 1000000, '#,##0') AS RowRange
AUTOGENERATE 1000000;

// ==============================================================================
// 2. Load and prepare the original data from Expense table.
// ==============================================================================
RawData:
LOAD
    RowNo() AS RowID,
    CUSTOMER_ID,
    Organization
RESIDENT Expense;

// ==============================================================================
// 3. Left Join the raw data onto the master scaffold.
// This could be the key step to ensure all 1,000,000 rows are kept...?
// Rows from the scaffold that don't have a matching RowID in RawData
// will have null values for CUSTOMER_ID and Organization.
// ==============================================================================
LEFT JOIN (RowScaffold)
LOAD
    RowID,
    CUSTOMER_ID,
    Organization
RESIDENT RawData;

// ==============================================================================
// 4. Create the final table for use in your Qlik sheet.
// So use If() statements to handle the null values from the Left Join,
// replacing them with 'No Data' or '---'.
// ==============================================================================
FinalTable:
LOAD
    RowID,
    RowRange,
    If(IsNull(Organization), 'No Data', Organization) AS Organization,
    If(IsNull(CUSTOMER_ID), '---', CUSTOMER_ID) AS CUSTOMER_ID
RESIDENT RowScaffold;

Please let me know because I want to solve this, please put me in right path and please feel free to make changes in the code and that will be fantastic.  Thanks a lot for your help

[marcus_sommer]

Yes, this should be also working. Means creating at first the the populated records and joining against them the native data. If I think about it - it's probably better as my first suggestion.

[SSark]

Hi,

I value your opinion, and I am looking forward to solving the following issues: 

When I start the sheet, one range is 1-1,000,000; select this range, then in the details table, the displayed Row number is correct, but for all rows, the value of the Organization field is now 'No Data'. It should display the organization's name randomly, such as 'Aviation', 'Boeing', and so on. 

In the table Organization field expression is: =If(Len(Trim(Alt(Organization, ''))) = 0, 'No Data', Organization)

Now the Organization Filter Pane first row is displayed 'No Data', how can this be removed from the filter? 

Selected 'Aviation' from the Organization filter and the details table displayed Aviation starting at Row No: 5380078, 5380079, till 5380081, then jumped to 5380096.

• How can I fix this so that the row number is correct, starts from 1 and goes up sequentially, and Organization can show 'No Data' in case of missing values? 

I can use help with code. your assistance is greatly appreciated. 

Thanks a lot.

[marcus_sommer]

The ordering should be automatically fitting because at first are the 1 M populated records with a continuous record-id and then the nativ data are loaded in the same way and joined per record-id. There won't be a gap within record-id's.

The above is now simplified for a single organization. For n organizations each one must get it's own 1 M of records and the join key must be combined of the record-id and the organization whereby the record-id needs to be related to organization respectively in other words the rowno() is not suitable else it needs recno() or iterno() with appropriate loops.

The 'no data' return might be already added for alle relevant fields within the dummy-records which would avoid the need to address the topic in the UI. Should it be within the UI the following auto-replace function for NULL is simpler:

coalesce(Organization, 'no data')

[SSark]

Hi,

Thank you!

I still mot be able to solve the issue. 

Would you kindly give me an example with code block please. It would be fine even with 500 rows and range could be 1-99, 100-199, 200-299 etc. It would me monumental help. 

Thank you very much.

[marcus_sommer]

Here a simplified example which performed the above hinted measurements:

t: load *, K & RecID as Key; load *, iterno() as RecID while iterno() <= 5; load chr(64 + recno()) as K autogenerate 5;
left join(t) load K, V, K & RecID as Key; load *, autonumber(recno(), K) as RecID inline [
K, V
A, 1
A, 3
B, 1
C, 2
D, 4
E, 1
E, 2
E, 5
];

and results in: