Load 10000 rows of data for each region

[ashishp]

Hi There 

I am using following script to load data. 

Master:

Load

RowNo() as NumberOfRows,
"Reporting Country Name",
"Accounting Period",
Currency,
"Company Number",
"Company Name",
"Client Executive Name",
"Client Segment",
"Mode of Entry",
"Transaction Number",
"Policy Number",
"Gross Premium",
"Net Revenue",
"Source System",
"Invoice Date",
"Invoice Nr",
"Responsibility Center",
"Invoice Number",
Region

In above script we have data around 15 Millions for all regions. I want to restrict data 10,000 rows for each region. 

There are around 8 regions.  Hence when selected Region Filter in report only 10,000 rows should see for selected region. 

Thanks for your help.  

[Mark_Little]

HI @ashishp 

You want to read up on Loops in Qlik Sense script. https://www.ometis.co.uk/loops-in-qlik-sense/

[vinieme12]

Try below

temp:
Load Distinct Region as R_Region SourceQvd;

For each reg in fieldvaluelist('R_Region')

    Fact:
    Load recno() as rows
    ,*
    From SourceQvd
    Where Region='$(reg)';

next
Drop table temp;

Inner Keep(Fact)
keeprows:
Load recno() as rows
AutoGenerate 10000;
Drop table keeprows;

exit Script;

[ashishp]

@vinieme12 Thanks for updates. 

This statement is showing error. 

Load Distinct Region as R_Region SourceQvd;

Can you please suggest?  

[ashishp]

The following error occurred:

Cannot open file: 'lib://Global MDS Operational Intelligence/data/claims/transformation/transformRevenueMaster.qvd Where Region=CE' (Native Path: *** System error: ***)

The error occurred here:

Fact: Load recno() as rows ,* From [lib://Global MDS Operational Intelligence/data/claims/transformation/transformRevenueMaster.qvd] Where Region='CE'

[vinieme12]

as below

Fact:

 Load

recno() as rows

,*

From [lib://Global MDS Operational Intelligence/data/claims/transformation/transformRevenueMaster.qvd](QVD)

Where Region='CE';

[Ruhulessin]

Hi @ashishp,

Would suggest to use the loop of @vinieme12, but slightly adjusted.

Regions:
LOAD DISTINCT
    Region
FROM ...Source...
;

FOR EACH vRegion IN FieldValueList('Region')
    
    First 10000
    Fact:
    LOAD *
    FROM ...Source...
    WHERE Region = '$(vRegion)'
    ;

NEXT

DROP TABLE Regions;

-Ruben