Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld May 10-12, Online and Free! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
eddysunn
Contributor III
Contributor III

Table Structuring

Hello Everyone,

I have a set of Excel files (one file for each country or one country-per-sheet in some cases) with sales data by year, month, item name etc., but without a country field.

I want to connect to all these files and create visualizations including all of them.

Do I have to create a separate field called Country and write country name there or is there other way I can use?

Edil

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

I think you need to create a Country field but you didn't do it manually. With file-functions like filebasename() you could read the filename from currently loaded file and with additional string-functions like subfield(), mid() and so on you could separate the Country value if there are further prefix/suffix within the filename.

Also the sheet-names could be read but for this you will need to load xls per ODBC like in this example: Loading Multiple Excel Sheets Dynamically along with file name and sheet name.

- Marcus

View solution in original post

6 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

I think you need to create a Country field but you didn't do it manually. With file-functions like filebasename() you could read the filename from currently loaded file and with additional string-functions like subfield(), mid() and so on you could separate the Country value if there are further prefix/suffix within the filename.

Also the sheet-names could be read but for this you will need to load xls per ODBC like in this example: Loading Multiple Excel Sheets Dynamically along with file name and sheet name.

- Marcus

View solution in original post

lironbaram
Partner
Partner

hi

if the country name is part of the filename

than you can use filename() in your script to add it as a new field in the table.

if the country name in in sheet level

you'll need to with the method that load excel via odbc connection

than every sheet is a table

OmarBenSalem
Partner
Partner

Hi Edil, I once did something similar to that; I had folder in which I had 3 excel files, each one contains the client, and its sales (sthing like this)

But each file have a name of a country : Tunisia.xls, Algeria.xls and so on; so every file was specific to a country;

What I did, is creating a new country field based on the name of the excel file :

here how it goes:

let vDir='D:\Formation\Flags examples\FlagConcat';

test:

LOAD '' as SalesMan,

     null() as Sales,

     '' as Pays

AutoGenerate 1;

LET vF=;


for each vF in filelist (vDir & '\*.xls' )

   let name= SubField('$(vF)', '\',5) ;

   let vName= '$(name)' ;

   

    Concatenate(test)

LOAD SalesMan,

     Sales,

     SubField('$(vName)','.' ,1) as Pays

FROM

(biff, embedded labels, table is Feuil1$);

next vF

NoConcatenate

Pays:

LOAD SalesMan,

  Sales,

     Pays

Resident test

where Sales<>Null();

DROP Table test;

beck_bakytbek
Master
Master

Hi E´dil,

do you have an example (like an screenshot) of your issue ?

´beck

beck_bakytbek
Master
Master

Hi Edil,

look at this: https://www.youtube.com/watch?v=CqvINEVAr8w

i hope that resolves your issue

beck

eddysunn
Contributor III
Contributor III
Author

Thanks everyone,

With all this headache I think I will just stick to adding a country field to the file.