topic Re: Selecting data from multiple Excel tables with same field names in App Development

Selecting data from multiple Excel tables with same field names

Tue, 06 Jun 2017 10:18:52 GMT

Hi all,

I have multiple excel files (*.xslx) containing data gathered over different dates. The excel files have the same sheet names and the same field names. The only data that is different from one file to another is the values of the fields.

Sample_File_Date1 :

Feature_Name Value_1 Value_2

4D TRAK 98% 75%

Automatic Coil Selection\AcsFcc 81% 65%

MRE 93% 40%

PCA 96% 21%

Sample_File_Date2 :

Feature_Name Value_1 Value_2

4D TRAK 94% 85%

Automatic Coil Selection\AcsFcc 82% 21%

MRE 0% 40%

PCA 67% 22%

In my load script, I am loading the excel files dynamically as below :

LOAD

Feature_Name

Value_1

Value_2

FROM [lib://../Sample_File_*.xlsx]

(ooxml, embedded labels, table is [SampleSheet]);

The problem I have is I have to display/calculate values based on the dates. That is, some of my charts use, for example, average of Value_1 only from the file Sample_File_Date2 , while elsewhere a pivot table might have to display the fields only from Sample_File_Date1 in a pivot table. What function(s) should I use to achieve this?

Any help is truly appreciated.

Regards,

Kanchana

Re: Selecting data from multiple Excel tables with same field names

swuehl — Tue, 06 Jun 2017 10:38:21 GMT

Create a new field in the LOAD that identifies the file source.

Instead of the wildcard '*' in the filename, I would use a FOR EACH loop and use the variable, something like

For Each File in FileList('[lib://../Sample_File_*.xlsx]')

Data:

LOAD

Feature_Name,

Value_1,

Value_2,

Subfield('$(File)', '\', -1) AS Filename

FROM [$(File)] (ooxml, embedded labels, table is [SampleSheet]);

Next File

edit: there is also a function FileName() you can have a look into, but there seems to be an open issue with latest QlikSense versions and xlsx files

Re: Function FileName() not working after upgrade to 3.2.3

Re: Selecting data from multiple Excel tables with same field names

Tue, 06 Jun 2017 10:49:37 GMT

Hi Stefan,

Thanks for your response.

However, I do not have any use for the file name as there are hundreds of such files that differ only in the date in which they were generated. Therefore, I have to use the date to filter out the data, such as the scenarios as below :

1. Display Feature_Name, Value_1, Value_2 from the table that was last generated (I use Max(date) function here) in a pivot table.

2. Calculate the Average of all Value_1 values from the table that was generated just previous to the latest table and display in a KPI.

etc.

I need to know specifically how to select the data in my chart - I am not sure whether set analysis would come in handy here, and if it would, how should I create my statements?

Regards,

Kanchana

Re: Selecting data from multiple Excel tables with same field names

swuehl — Tue, 06 Jun 2017 10:52:47 GMT

As far as I understood, you need to parse the date from your filename, right?`

Above was just a sample, use string functions, like Textbetween() or Subfield() to filter the dates from the filename.

After you parsed the dates and stored them into a field, you can filter your date using set analysis or filter panes or conditional functions just like you filter on any other attribute.

Re: Selecting data from multiple Excel tables with same field names

Tue, 06 Jun 2017 11:11:11 GMT

I have the date information in two separate places, one an xml file which contains some generic information on the excel file as below :

<records xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<record>

<File_No>1</File_No>

<File_Date>05/02/17</File_Date>

<File_Stream>Atlmainrecon</File_Stream>

<File_SWID>404</File_SWID>

</record>

<record>

<File_No>2</File_No>

<File_Date>06/01/17</File_Date>

<File_Stream>Atlmain</File_Stream>

<File_SWID>339</File_SWID>

</record>

</records>

I have loaded this file also in the script as

SampleCrossTable:

LOAD

File_No as "File No",

File_Date as "File Date",

File_Stream as "File Stream",

File_SWID as "File SWID"

FROM [lib://*/Summary.xml]

(XmlSimple, table is [records/record]);

From this, I can get the date information, say, the latest date as follows :

LOAD

Date(max("File Date")) as MaxDate

resident SampleCrossTable;

Two, the same date information is also available from the filename, which I have extracted this way within the load script:

Date(Mid(FileName(), 12, 11)) as "File Date"

But my question is, how can I correlate the MaxDate from the load script with the corresponding Excel file and use it in my charts to get the data for the scenarios that I mentioned in my previous question.

I have tried using conditional statements such as

if([File Date] = MaxDate), do something..

but this doesn't get me any data. Where could I be going wrong?

Regards,

Kanchana

Re: Selecting data from multiple Excel tables with same field names

swuehl — Tue, 06 Jun 2017 15:23:01 GMT

If something like

if([File Date] = MaxDate), do something..

does not work, there are two common issues (besides that if() function should show at least two arguments, THEN branch being second, an optional ELSE branch being third. Have a look at the HELP pages for full syntax and examples. I guess above pseudo code is just not based on your real expression syntax)

a) both dates should be read in as dates by Qlik, having a numerical representation

Why don’t my dates work?

Get the Dates Right

b) You probably need to use an aggregation function around the if() conditional, i,e, rather use

=Sum( If( X=Y, Z))

than

=If( X=Y, Sum(Z))

I say probably, because the correct syntax is depending on the scope of your expression and your requirement.

For example, second expression may work when it's used with either X or Z as dimension.

Use Aggregation Functions!

If this is still not getting you any further, then please add more context to your request, best by posting a small, reloadable sample application.

Re: Selecting data from multiple Excel tables with same field names

d_prashanthredd — Tue, 06 Jun 2017 16:08:31 GMT

Hi Kanchana,

What I understood in this case..

You have multiple files and loaded data with the date which is available in file name.
Also a master table which contains file n date name.
From above 2, you only bother about Max date.

When you have same columns, automatically data will be appended. For ex. In file 1 you have 10 records with date, col1, ..

and file 2 with 10 records with diff date, col1, ..

Now you will have 20 records after load..

To get your desired output have a variable to hold max date from your other table/master.

Next, resident load from base data with date condition = max date from the variable.

I hope, it will solve your problem.

Re: Selecting data from multiple Excel tables with same field names

Wed, 07 Jun 2017 09:45:39 GMT

Hi Prashanth,

Thank you for your response. But as I was discussing with Stefan earlier, I have the max date stored in a variable at the time of loading the files. And my charts and calculations work perfectly when only 1 excel file is present in the directory. But as soon as I load more than one file (since they all have the same column names), the charts are unable to display the right data from the date in which I am interested.

I am rewriting the code from the earlier posts for your benefit :

--------------------------------------------------------------------------------------------------------

PART 1 : This is my master file with the generic information:

<records xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<record>

<File_No>1</File_No>

<File_Date>05/02/17</File_Date>

<File_Stream>Atlmainrecon</File_Stream>

<File_SWID>404</File_SWID>

</record>

<record>

<File_No>2</File_No>

<File_Date>06/01/17</File_Date>

<File_Stream>Atlmain</File_Stream>

<File_SWID>339</File_SWID>

</record>

</records>

I have loaded this file in the script as :

SampleCrossTable:

LOAD

File_No as "File No",

File_Date as "File Date",

File_Stream as "File Stream",

File_SWID as "File SWID"

FROM [lib://*/Summary.xml]

(XmlSimple, table is [records/record]);

From this, I can get the date information, the max date as follows :

LOAD

Date(max("File Date")) as MaxDate

resident SampleCrossTable;

And the date just previous to the latest as :

LOAD

Date(max("File Date")) as MaxDate

resident SampleCrossTable where not exists(MaxDate,"File Date");

I have checked that I get both the dates as expected.

---------------------------------------------------------------------------------------------------------------

PART 2 : These are my multiple excel data files with the information that has to be selectively used in chart display or for some calculations based on the dates :

Sample_File_Date1 :

Feature_Name Value_1 Value_2

4D TRAK 98% 75%

Automatic Coil Selection\AcsFcc 81% 65%

MRE 93% 40%

PCA 96% 21%

Sample_File_Date2 :

Feature_Name Value_1 Value_2

4D TRAK 94% 85%

Automatic Coil Selection\AcsFcc 82% 21%

MRE 0% 40%

PCA 67% 22%

In my load script, I am loading the excel files dynamically as below :

LOAD

Feature_Name

Value_1

Value_2

FROM [lib://../Sample_File_*.xlsx]

(ooxml, embedded labels, table is [SampleSheet]);

---------------------------------------------------------------------------------------------------------------

PART 3 : Now, let us say I want to display the contents of the sample file with the latest date in a pivot table, this is the code I have written in the pivot table :

ROW :

[Feature_Name]

MEASURE :

Only({<[File Date] = MaxDate>} [Value_1])

This is where the problem begins as the values are not being filtered. It only displays those values in the chart when they are the same across all the excel files. The other values are being displayed as null.

-----------------------------------------------------------------------------------------------------------------------

I don't know if I have missed anything, and any help would be great!

Regards,

Kanchana

Re: Selecting data from multiple Excel tables with same field names

swuehl — Wed, 07 Jun 2017 10:09:07 GMT

And how do you want to display the values if they are not the same?

Only() does work as designed to show only identical values:

The Only Function

You either need to add a dimension (like Value_1) to create more possible rows per combination of dimensional values or use a different aggregation function, like Concat():

=Concat(DISTINCT {<[File Date] = MaxDate >} [Value_1], ', ')

Re: Selecting data from multiple Excel tables with same field names

d_prashanthredd — Wed, 07 Jun 2017 10:32:01 GMT

Hi Kanchana,

I think you can do this way to have a relationship with dates to do some filter stuff or any in front end.

Sample_File_Date1 :

load Feature_Name, Value1, Value2, 'Date1 (ex: 5/6/2017)' as FileDate .....;

Note: Date1 can be extracted from the filename

Feature_Name Value_1 Value_2 FileDate

4D TRAK 98% 75% 5/6/2017

Automatic Coil Selection\AcsFcc 81% 65% 5/6/2017

MRE 93% 40% 5/6/2017

PCA 96% 21% 5/6/2017

Sample_File_Date2 :

Feature_Name Value_1 Value_2 FileDate

4D TRAK 94% 85% 4/6/2017

Automatic Coil Selection\AcsFcc 82% 21% 4/6/2017

MRE 0% 40% 4/6/2017

PCA 67% 22% 4/6/2017

Sorry, If I'm talking about something esle.

Re: Selecting data from multiple Excel tables with same field names

Thu, 08 Jun 2017 07:05:26 GMT

Hi Stefan,

I do not need to worry if the values are the same or not, the requirement is simply that I display the values as they are, but only from the latest results (that is why I calculate the MaxDate). In addition, I also need perform some more calculations based on the date, such as compare the values from the latest report with those from the last report, determine averages etc.

I think I cannot use Concat() because I need to display only one value in a pivot table. Just for the sake of simplicity of understanding, assume I have to load all the excel files, but display the data values (as is) only for the latest file in a pivot table.

Regards,

Kanchana

Re: Selecting data from multiple Excel tables with same field names

Thu, 08 Jun 2017 07:08:30 GMT

Hi Prashanth,

Yes, that's exactly what I want to do - to establish a relationship between the data in the excel sheets and the date on which they were created. But I am unable to get the information for the individual dates once all the excel sheets have been loaded in the script.

I even tried creating the Date field for each row in the excel sheet before I loaded them like you suggested (did I assume correctly?), but once the data is loaded, the association seems have got lost.

Regards,

Kanchana

Re: Selecting data from multiple Excel tables with same field names

d_prashanthredd — Thu, 08 Jun 2017 07:29:48 GMT

Hi Kanchana,

Is your file(s) name contains date? If yes, we can extract date straight away.

ex: I have a file with name "MAC_R2_CR_PBTV_ACCOUNTS_08-June-2017.xlsx " and which is having 2 columns ID and Name.

Below is my script to get data from the file along with the date.

LOAD ID,

Name,

Date(Date#(SubField(FileBaseName(),'_',-1),'DD-MMM-YYYY')) as DateExtracted

FROM

(ooxml, embedded labels, table is Sheet1);

Output:

If possible, share your sample app with couple of files data.

Re: Selecting data from multiple Excel tables with same field names

Thu, 08 Jun 2017 08:21:57 GMT

Hi Prashanth,

Yes, I have the dates in my file names and I have extracted them just as you have shown here. But when there is more than one file, this is how my pivot chart looks when I try to display values only from one particular date.

The values are displayed only if they are the same in all the files. Otherwise, it shows a blank. I am not sure what expression I need to use to display only those values that are from the MaxDate.

Regards,

Kanchana

Re: Selecting data from multiple Excel tables with same field names

swuehl — Thu, 08 Jun 2017 09:03:34 GMT

I would start with checking if the table chart shows what you want when you select the file date in a filter pane (maybe remove the set expression from your Only() function to avoid a conflict between the selection and the set expression).

If it does work, then your set expression is not correct.

{<[File Date] = MaxDate >}

is basically copying the selection in field MaxDate to [File Date]. I assume that you don't have an active selection in this field?

And from what I've learned above, your MaxDate field shows two dates, but you want to show the data for the min date of the two?

Check if

=Only( If( [File Date] = Minstring(MaxDate), [Value_1]))

returns what you want (without any selection in the date fields).

If yes, you can create also a set expression with this filter, but you need to take care of some Qlik specifics (basically value formatting in the set modifier):

Dates in Set Analysis

Maybe something like

=Only({<[File Date] = {'$(=Date(Max({1} [File Date],2)))'} >} [Value_1])

edit: and again, all help would be much easier if you could provide a small sample application

Re: Selecting data from multiple Excel tables with same field names

rospigliosi — Thu, 08 Jun 2017 09:15:37 GMT

Hi Kanchana,

in the excel file in wich format are the numers? Are they numbers or text?

Why your percentage are alligned on the left?

Perhaps You need to convert them in numbers.

Re: Selecting data from multiple Excel tables with same field names

d_prashanthredd — Thu, 08 Jun 2017 09:30:37 GMT

So, you are talking about '4D TRAK' and '4D TRAK XD'... Ryt?

Upon particular date (or any other filter selection), it will display the values (Feature) which are related to the date.

I believe 4D TRAK XD is in ur data set for that period and may be the values for the fields which you used in expression may be blank.

Easy way to get rid of blank rows is by unchecking "Include Null Values".

Re: Selecting data from multiple Excel tables with same field names

Mon, 12 Jun 2017 07:10:53 GMT

Hi Stefan,

Sorry for the delay in reponding to your mail - I was trying all the suggested options and found that filtering the date in the way you have suggested works in my case. Thank you so much for your kind help, my problem is solved now!

Regards,

Kanchana

Re: Selecting data from multiple Excel tables with same field names

Mon, 12 Jun 2017 07:12:16 GMT

Thanks Camillo. Yes, I have purposely converted them to text for display purposes to have left alignment. But that doesn't interfere with my calculations as the text format is only for display.

Regards,

Kanchana

Re: Selecting data from multiple Excel tables with same field names

Mon, 12 Jun 2017 07:14:01 GMT

Hi Prashanth,

No, this blank display was a problem across all data when the values were different across the files. But I have solved it by using Stefan's suggestion for filtering the date using Set analysis.

Thanks so much for all your help!

Regards,

Kanchana