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

[qliksenseadminthomascook]

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.

[Mark_Little]

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 

[rafaelencinas · Accepted solution]

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;

[qliksenseadminthomascook]

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,

X,

Y,

Z

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,

X,

Y,

Z

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;

NEXT

DROP TABLE LargeAccountsList;