Range of text fields

[Anonymous]

Hi

Thanks in advance for your feedback.

I need to get the 'latest' value from 6 text fields, tried using something like rangemax but without success.

Here's the load script example

LOAD id as [Id],

  type1 as [Type 1 Desc],

  type2 as [Type 2 Desc],

  type3 as [Type 3 Desc],

  type4 as [Type 4 Desc],

  type5 as [Type 5 Desc],

  type6 as [Type 6 Desc];

SQL SELECT id,

type1desc,

type2desc,

type3desc,

type4desc,

type5desc,

type6desc

FROM table;

Typical Rows would look like:

id | type1 | type2 | type3 | type4 | type5 | type6

1 | Red | Blue | null | null | null

2 | Red | null | null | null | null

3 | Red | Blue | Green | null | null

4 | Blue | null | null | null | null

type2 can only be populated if type1 is populated

type3 can only be populated if type1 and type2 is populated

and so on..

The required result would produce the 'latest' of the fields 1-6 which have a value:

1 Blue

2 Red

3 Green

4 Blue

So I need something like this but rangemax obviously doesnt work for this

rangemax(type1,type2,type3,type4,type5,type6) as [Type Max],

[CELAMBARASAN · Accepted solution]

Hi,

     Check with the following.Before the load the main table.

     MainTable:

LOAD id as [Id],
       type1 as Type1,
       type2 as Type2,
       type3 as Type3,
       type4 as Type4,
       type5 as Type5,
       type6 as Type6;
SQL SELECT id,
          type1desc,
          type2desc,
          type3desc,
          type4desc,
          type5desc,
          type6desc
FROM table;
     OutputTable:    
     LOAD
               id,
               Pick(RangeTextCount(Type1,Type2,Type3,Type4,Type5,Type6),Type1,Type2,Type3,Type4,Type5,Type6) as Output
     Resident
               MainTable;

Hope it helps

Celambarasan

[Anonymous]

Worked perfectly - thanks for your quick response!