On Demand parameters when list value is empty

[Pierre Judas]

Hello

 

First of all thanks for taking some time to read this.. i am using Spotfire since some month now but we have been learning all alone from 0 so we are far from what could be done with such a tool but we are quite happy using it now :)

 

Whatever, here is my question :

I am retrieving data from Spotfire (that obvious) and instead of getting multiple prompt I using an excel file with all the value I need and I manage to get it as parameter into my download using "On-Demand" functionnality (that first step has been a great help)

The image attached is the 2 "Define input" I made :

But now I would like to now how I can manage with this method empty or not value in some column of my excel file.

As en exemple :

1st extraction :

I need all my reference sell to germany between 01/06 and the 30/06

In my excel parameter file I get :

- 1st column "selling country" with the value "Germany"

- 2nd column "Selling date" with the min & max date (01/06 & 30/06).

 

 

 

Selling Country

Selling Date

 

 

Germany

01/06/2020

 

 

 

30/06/2020

 

 

 

Through the "On demand" setting menu i linked the 2 column "selling country" & "Selling date" from my database & my excel sheet.

It works fine.

2nd extraction:

I need to get all my seels from the 01/06 to 30/06 whatever the country is, so i keep the max & min date into excel sheet and delete "Germany".

- 1st column "selling country" no value.

- 2nd column "Selling date" with the min & max date (01/06 & 30/06).

 

 

 

Selling Country

Selling Date

 

 

 

01/06/2020

 

 

 

30/06/2020

 

 

 

The result is empty as i guess spotfire is trying to retrieve all the reference with selling country = emplty... wich is none...

So My quesiton is, how can i resolve this issue.

I guess using an expression from the "Define input" windows, but i did not understand how...

I have tried something like :

if([Param_Date - Parametre extraction].[selling Country] Is Not Null,[Param_Date - Parametre extraction].[selling Country],all value)

But i did not know how to mention the "all value"

Thanks for reading & maybe helping me :-)

Pierre

[Pierre Judas]

Hello.

Any idea on this question

thanks

[Fabian Duerr]

Sorry, I'm not getting it by 100%...

 

Why do you want to do any operation in Excel I would always get the raw data to Spotfire and then continue with the analysis.

 

Anyhow, if you provide some sample data I can have a look at it. I have not used this functionality so often yet, so there might be something to learn.

[Pierre Judas]

Thanks to take some time for me FabD :-)

Here is an exemple of my use ;

1st : i create an Excel file with the parameters i will use :

in this exemple with the range date i will need and the 2 countries I need data (Germany & Belgium)

 

2nd : I am loading this file into Spotfire as a table

 

3rd : I am using value on this table into my On demande settings (Range Max & Min for Date and values for country

 

 

Result : This works fine, i get the data i need from the 01/09 to the 03/09 for destination country Germany & Belgium.

 

 

Now I need to get the same data for all countries, so I go inmy excel file and delete the 2 previous value in Coutry as below:

 

Then reload the table into Spotfire :

 

And relaunch the query and get an empyt relsult (as i think query is looking for "Empty" country) :

 

So my question is : Is it possible to get all countries using this method

If you have any other leads to do it, don"t hesitate to share it with me ;-)

 

I read some info about using Expression of string list bu have not been to use it.

Thanks

[Fabian Duerr]

So it seems that Spotfire is searching for a NA country which doesn't exist in the table.

 

Of course you could fill the country column with all available countries. But I guess you don't know all. Otherwise you would have done that already...

 

So what happens if you don't use the country column at all for your second extract Either by removing it from the settings or by clearing the input...

[Pierre Judas]

Hi FabD,

Yest ur right, I could list all country or not use this column for the second extract but i would like to have the simpliest way to use it so user doest not have to change anything in the DXP.

Thanks for your help, i will continue to look for a solution that be OK for my needs.