Get first fieldname name with a value condition

[#1]

Hi,

I want to have a new column on a table that gives me the first column that has a specific value.

So,

I have this

Header 1	Header 2	Header 3	Header 4	Header 5
Name	Month1	Month2	Month3	Month4
A	0	0	3	3
B	0	4	0	1
C	0	3	2	1
D	1	0	0	1

I would like to add a column with the name of the first column that has a value higher than 0.

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6
Name	Month1	Month2	Month3	Month4	FirstMonthSales
A	0	0	3	3	Month3
B	0	4	0	1	Month2
C	0	3	2	1	Month2
D	1	0	0	1	Month1

So, the FirstMonthSales column should have the first month that has a value higher than 0.

Thanks,

Dani

[salto]

Hello Dani,

First load the table data into a resident table:

ResidentTable:

Load

Name,

Month1,

Month2,

Month3,

Month4

From Data.xls(biff, embedded laels, table is Sheet1$)

Then reload the table data and put an IF to check the first month with sales:

Data:

Load

     *,

     if(Month1>0,Month1,if(Month2>0,Month2,If(Month3>0,Month3,Month4))) as FirstMonthSales

resident ResidentTable;

drop table ResidentTable;

Hope this helps!

[pgrenier]

Hello Dani,

I guess it depends on what you are ready to change in order to obtain your result. Please find an example file based on a CrossTable approach of your result set. In that file, you shall see that using a Pivot Table will not permit to see the desired result, but if you opt to do a little hard coding, you may get your desired presentation with a Straight Table.

Otherwise, by joining objects within a container, you may also simulate the result using both types of objects.

Cheers,

Philippe