Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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')
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')
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
Hi Sunny,
These are fields.
Total fields 69 every month one field get increase .
Now have Jan-10 to Dec-15.
Thanks
Are you using CrossTable Load to make them rows instead of keeping them as Column?
No. I am just fetching data directly.
Yes I will use cross table functionality then use your above solution .
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;
Ya if you use CrossTable you can then change those column using the expression provided earlier
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;
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