topic Re: Table Structuring in Visualization and Usability

Table Structuring

Anonymous — Tue, 28 Feb 2017 10:46:34 GMT

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

Re: Table Structuring

marcus_sommer — Tue, 28 Feb 2017 11:13:33 GMT

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

Re: Table Structuring

lironbaram — Tue, 28 Feb 2017 11:21:14 GMT

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

Re: Table Structuring

OmarBenSalem — Tue, 28 Feb 2017 11:34:39 GMT

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;

Re: Table Structuring

beck_bakytbek — Tue, 28 Feb 2017 12:09:00 GMT

Hi E´dil,

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

´beck

Re: Table Structuring

beck_bakytbek — Tue, 28 Feb 2017 12:12:18 GMT

Hi Edil,

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

i hope that resolves your issue

beck

Re: Table Structuring

Anonymous — Tue, 28 Feb 2017 20:26:13 GMT

Thanks everyone,

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