topic Re: Storing data into multiple files based on the line items in an account in App Development

Storing data into multiple files based on the line items in an account

qliksenseadminthomascook — Thu, 12 Jun 2025 06:00:16 GMT

Hi all!!!

I am working on a table output which we store into a CSV file. Here we have a field Account Numbers in which each account number will be repeated multiple times. If the line items for one account number is greated than 400,000 then we have to store them in a single CSV file with that account number included in the file name. The rest of the account numbers should come in a single dump.

Thanks in advance.

Re: Storing data into multiple files based on the line items in an account

Mark_Little — Thu, 12 Jun 2025 13:53:26 GMT

Hi,

Where are you trying to do this, In a Qlik sense app script?

it wouldn't be the best place to do this, but it would be possible.

Firstly create a table of the Account Number and record count.

then you would need to create a loop that loops through where count > 400,000, using the peek function to populate a variable for the where and file name argument.

Then you would need a second loop that created a table of all other account numbers then outputs.

Mark

Re: Storing data into multiple files based on the line items in an account

rafaelencinas — Thu, 12 Jun 2025 14:28:57 GMT

Hi @qliksenseadminthomascook

1 - Load your data

2 - Create a column ID with RecNo() ( it will be filled with the number of the row )

3 - Create a new column FileNumber - > ID / 400.000 (it will generate a new column with divisor number)

4 - Peek the minimum and maximum value of FileNumber

5 - Create a for statement for i = Min to Max and store your files

[sales_data_sample]:

LOAD

[ORDERNUMBER],

[QUANTITYORDERED],

[PRICEEACH],

[ORDERLINENUMBER],

[SALES],

RowNo() as ID,

Floor(RowNo() / 1000) as FileNumber

FROM [lib://Downloads/sales_data_sample.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Temp:

Load Min(FileNumber) as MinFile

,Max(FileNumber) as MaxFile

Resident sales_data_sample;

Let vMin = Peek('MinFile',0,'Temp') ;

Let vMax = Peek('MaxFile',0,'Temp') ;

Drop table Temp;

trace =================;

trace $(vMin) - $(vMax);

trace =================;

for i = vMin to vMax

temp:

NoConcatenate

Load * Resident sales_data_sample

where FileNumber = $(vMin);

store temp into [lib://Downloads/temp_$(i).qvd](qvd);

drop table temp;

Next i;

Drop table sales_data_sample;

Re: Storing data into multiple files based on the line items in an account

qliksenseadminthomascook — Fri, 13 Jun 2025 06:02:18 GMT

Hi @rafaelencinas ,

This works if I want all the csv files separately for each and every 1000 line items. However, I wanted is for the CSV to be created based on the account number. If the account number has 400,000 or more records that data should come to one file and all the other data should come to one file. Thank you for your help, I have achieved it with the below script:

MainTable:

Load *,

IF(RecCount >= 400000, 'Large', 'Small') AS CountFlag;

LOAD
Account,

COUNT(Account) AS RecCount

From qvd path;

Noconcatenate

SmallAccounts:

Load Account,

Resident MainTable

where CountFlag = 'Small';

Store SmallAccounts into [filepath/filename_SmallAccounts.csv](txt);

drop table SmallAccounts;

LargeAccountsList:
LOAD DISTINCT Account
RESIDENT MainTable
WHERE CountFlag = 'Large';

FOR i = 0 TO NoOfRows('LargeAccountsList') - 1

LET vAccountRaw = Peek('Account', $(i), 'LargeAccountsList');

LET vAccount = '$(vAccountRaw)';

// Load data for current account
LargeAccountData:

Load Account,

Resident MainTable

WHERE Account = '$(vAccountRaw)';

// Check if any data exists before store
//IF NoOfRows('LargeAccountData') > 0 THEN

STORE LargeAccountData INTO [filepath/filename_$(vAccount).csv](txt);

DROP TABLE LargeAccountData;

DROP TABLE LargeAccountsList;