Store multiple csv based on country names

[Dipesh]

Hi,

I have a hug QVD file with multiple columns and i have to store CSVs file for each country name.

Country

India, US, UK, ....etc (more than 130)

QVD File

Field1, Field2, .... Country, ZIP, ...etc.

Can anyone help me on script where i can loop country list and save each CSV file based on this name to the folder.

[pedrobergo]

Hi @Dipesh 

You can try these code:

If Not IsNull(NoOfRows('QVD File')) then
Aux:
Load Distinct
CountryName
Resident RecordSet;

For vFileNo = 0 to NoOfRows('Aux')-1

Let vCountry = Peek('CountryName',$(vFileNo),'Aux');
Let vArqCSV = '[lib://:DataFiles/' & '$(vCountry).csv]';

SourceTable:
NoConcatenate
Load Distinct * Resident RecordSet
Where CountryName='$(vCountry)';

If Not IsNull(NoOfRows('SourceTable')) then
Store SourceTable Into $(vArqCSV) (txt);
Drop Table SourceTable;
EndIf

Next vFileNo;

EndIf;

Drop Table Aux;
Drop Table RecordSet;

Good luck,

Pedro

[Dipesh]

Thank you, But i don't need to create CSVs for all markets in QVDs. I have to do only for few listed countries with wildmatch ([Market], "*Russia*", "*Turkey*",..........).

and in QVDs there can be also multiple market name in single row. 

Example :- ID 12345 Market - Russia-RUS; Turkey-TUR; Egypt-EGY

[pedrobergo]

Ok @Dipesh 

Just  use a if below the line Let vCountry = Peek('CountryName',$(vFileNo),'Aux');

Let vCountry = Peek('CountryName',$(vFileNo),'Aux');

if WildMatch('$(vCountry)',"*Russia*", "*Turkey*",..........)>0 Then

(...) 

Endif;

Next vFileNo;

EndIf;

Drop Table Aux;
Drop Table RecordSet;

[Dipesh]

Thank you

I have tried but its gave me error saying file not found. Not sure what i am doing wrong.

I have attached sample file might that will help.

[pedrobergo · Accepted solution]

Hi @Dipesh ,

The problem is that the Countries field at QVD File isn't the same that User Need Countries file.

• QVD File Country example: Lebanon ; Georgia
• User Need Country example: Lebanon

So you need to make some data cleansing this data field.

Follow this procedure that works with these field examples:

[UserCountries]:
LOAD
Countries as Country
FROM [lib://DataFiles/User Need Countries.xlsx]
(ooxml, embedded labels, table is Sheet1);

[QvdFile]: // Was AUX Table
LOAD
ID,
Countries,
Sales
FROM [lib://DataFiles/QVD File.xlsx]
(ooxml, embedded labels, table is Sheet1);

For vFileNo = 0 to NoOfRows('UserCountries')-1

Let vCountry = Peek('Country',$(vFileNo),'UserCountries');
Let vArqCSV = '[lib://:DataFiles/' & 'ARQ_$(vCountry).csv]';

SourceTable:
NoConcatenate
Load * Resident [QvdFile]
Where WildMatch(Trim(SubField(Countries,';',1)),'$(vCountry)')>0 ;

If NoOfRows('SourceTable')>0 Then
Trace Gravando $(vArqCSV)...;
// Store SourceTable Into $(vArqCSV) (txt);
Drop Table SourceTable;
EndIf;

Next vFileNo;

Drop Table QvdFile;
Drop Table UserCountries;

[Kushal_Chawda]

@Dipesh  

Create excel file to list down the countries to be filtered. Make sure that country names should exactly match with qvd data.

// Rename the contry field from excel to match with field name in QVD
list_countries:
LOAD Countries as Country
FROM
[lib://files/User Need Countries.xlsx]
(ooxml, embedded labels, table is Sheet1);

qvd_data:
LOAD *
FROM [lib://qvd/data.qvd] (qvd)
where Exists (Country);

DROP Table list_countries;

for i=1 to FieldValueCount('Country')

let vCountry= FieldValue('Country',$(i));

country_data:
NoConcatenate
LOAD *
Resident qvd_data
where Country = '$(vCountry)';

STORE country_data into lib://Data/data_$(vCountry).csv(txt);

DROP Table country_data;

NEXT

DROP Table qvd_data;

[Dipesh]

Thank you so much it works as expected, I just add else and drop table each time when there is no records. As qlik was creating SourceTable, SourceTable1, SourceTable2, SourceTable3...... and would not able to find "If NoOfRows('SourceTable')>0 "

If NoOfRows('SourceTable')>0 Then
Trace Gravando $(vArqCSV)...;
Store SourceTable Into $(vArqCSV) (txt);
Drop Table SourceTable;

Else

Drop Table SourceTable;

EndIf;

[angelaecheverri]

Hi

¿May I have the app in orden to understand how you see the data?