Dynamically select the 2nd last column from XLS data source while loading data into Analysis file

[Shubh M]

hello everyone

So I am in situation where I need to pick the 2ndlast column from XLS data source, while loading data into Spotfire Analysis file. More details below.

My XLS data source looks like attached(figure 1).So this snapshot shows values for 3 KPIs across different dates of month. I need to pick the value at last day of month (30th/31stday). If the month is of 30 days, I get the data files with columns till 30thand the number of columns increases if month has 31 days. And I need to pick the KPI2 value from last day of the month.

My output in the Spotfire after loading the analysis file (after dynamically picking 30th/31st day columns), should look like attached (figure 2).

 

I tried looking into the transformations, but couldnt find anything. Can you share some ideas on this Thanks

 

 

Regards

Shubs