Month Year field

[prma7799]

Hi All,

I have multiple fields like below

SALES UNIT JAN-10

SALES UNIT FEB-10

SALES UNIT MAR-10

SALES UNIT APR-10

I want output like below

Apr-10

May-10

Jun-10

Jul-10

[sunny_talwar · Accepted solution]

Are these field names or values within a field?

If field value then may be this:

Date(Date#(Right(FieldName, 6), 'MMM-YY'), 'MMM-YY')

[Anonymous]

Hi,

You have to options:

1. You can use this:  SubField(<FieldName>,'|',SubStringCount(FieldName,' ')+1

     or

2. You can: RIGHT(<FieldName>, 6)

​It will be more beneficial if this was done in the backend script

[prma7799]

Hi Sunny,

These are fields.

Total fields 69 every month one field get increase .

Now have Jan-10 to Dec-15.

Thanks

[sunny_talwar]

Are you using CrossTable Load to make them rows instead of keeping them as Column?

[prma7799]

No. I am just fetching data directly.

Yes I will use cross table functionality then use your above solution .

[sunny_talwar]

May be something along these lines if you are not using CrossTable Load

Table:

LOAD Concat(PurgeChar(@1, '#'), '|', Order) as List;

LOAD @1,

  RecNo() as Order

FROM

Community_200222.xlsx

(ooxml, explicit labels, table is Sheet1, filters(

Transpose()

));

LET vList = Chr(39) & Peek('List') & Chr(39);

DROP Table Table;

FinalTable:

LOAD *

FROM

Community_200222.xlsx

(ooxml, no labels, header is 1 lines, table is Sheet1);

For i = 1 to NoOfFields('FinalTable')

  LET vFieldOld = '[' & FieldName($(i), 'FinalTable') & ']';

  LET vFieldNew = '[' & Date(Date#(Right(SubField($(vList), '|', $(i)), 6), 'MMM-YY'), 'MMM-YY') & ']';

  RENAME Field $(vFieldOld) to $(vFieldNew);

NEXT i;

[sunny_talwar]

Ya if you use CrossTable you can then change those column using the expression provided earlier

[sunny_talwar]

Script for CrossTable load

Table:

CrossTable(MonthYear, Data)

LOAD ID,

    [SALES UNIT JAN-10],

    [SALES UNIT FEB-10],

    [SALES UNIT MAR-10],

    [SALES UNIT APR-10],

    [SALES UNIT MAY-10],

    [SALES UNIT JUN-10]

FROM

Community_200222_v1.xlsx

(ooxml, embedded labels, table is Sheet1);

FinalTable:

NoConcatenate

LOAD ID,

  Date(Date#(Right(MonthYear, 6), 'MMM-YY'), 'MMM-YY') as MonthYear,

  Data

Resident Table;

DROP Table Table;

[prma7799]

Hi sunindia‌,

I want to implement incremental load here.

Means every month my one month will increase so for that can we use incremental load here?

Thanks