Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All ,
i have Period_name column in my source table but date format is different i wants to change in( dd-mm-yyyy) format . plz give reply i can anyone solve this issue -
source sheet i attached
Try this:
Table:
LOAD If(IsNum(PERIOD_NAME), PERIOD_NAME, Date(Date#(Replace(PERIOD_NAME, '_Adj',''), 'MMM-YY'))) as Date
FROM
[DATE FORMATE .xlsx]
(ooxml, embedded labels, table is TB01_20150924_120040);
Not sure what you want to see for something like this -> Mar_Adj-12?
Hi Anil,
If you don't have date in your period_date, it would be difficult to get date, but you get the format like...
=Date(date#(PERIOD_NAME,'MON-YY'),'MM/YYYY')
Happy to help!
Sreeni
HI Sunny ,
some dates coming with adjustment period and those date i need to calculate with given current month
suppose if date is Mar_Adj-12 then i need to convert in "01-03-2012"
Anil,
I recommend, cleanse the data while loading as we don't have any direct approach.
@sunny t - please correct me.
Regards,
Sreeni
Try this
Date(Replace(Replace(Replace(PERIOD_NAME,'_Adj','-1'),'Mar',3),'-','/'),'DD-MM-YYYY') as PERIOD_Date,
LOAD
PERIOD_NAME,
Date(Replace(Replace(Replace(PERIOD_NAME,'_Adj','-1'),'Mar',3),'-','/'),'DD-MM-YYYY') as PERIOD_Date,
ACCOUNT_CODE,
BEGIN_CR,
BEGIN_DR,
JE_CATEGORY,
JE_SOURCE,
LEDGER_ID,
LEDGER_NAME,
PERIOD_CR,
PERIOD_DR,
PERIOD_NUM,
PERIOD_YEAR,
SEGMENT5_DESC
FROM
[DATE FORMATE .xlsx]
(ooxml, embedded labels, table is TB01_20150924_120040);
Try this:
Table:
LOAD If(IsNum(PERIOD_NAME), PERIOD_NAME, Date(Date#(Replace(PERIOD_NAME, '_Adj',''), 'MMM-YY'))) as Date
FROM
[DATE FORMATE .xlsx]
(ooxml, embedded labels, table is TB01_20150924_120040);
Something like Date(MonthStart(Date#(purgechar(PERIOD_NAME,'_Adj'),'MMM-YY')),'MMM-YY') as Period_Date
Try like:
Load *,
Date(Alt(Date#(PERIOD_NAME, 'MMM-YY'),Date#(PERIOD_NAME, 'MMM_Adj-YY')), 'DD-MMM-YY') as NewDate
Inline [
PERIOD_NAME
Mar-10
Mar_Adj-12
Mar_Adj-12
Jun-12
Jun-12
Mar_Adj-13
Mar-10
Mar_Adj-12]
Dear Sunil,
thanks for help but its not working , its showing blank