Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi guys,
I am not sure should use applymap or not, or other solutions.
Request:
Client give me a data file with year(2015), version(1st Half, 2nd Half). Client wants to add a target date for all 1st Half - > 31/08 , 2nd half to -> 31/12.
How should i do it?? any examples??
Rgds
Jim
You can supply the Year field as a parameter to the MakeDate() function:
if(Version='1st half', MakeDate(Year,8,31), if(Version='2nd half',MakeDate(Year, 12,31))) as TargetDate
-Rob
Could you try with IF statement?
IF(yourfield<'31/08/2015','1st Half','2nd Half') as Yearhalfs.
Assuming there are no other dates?
or similar syntax?
Hi Jim,
I don't think applymap will be required here. If the target date you want to add to both the versions are fixed then you can try something like this.
Load
*,
if(Version='1st half','31/08/2015', if(Version='2nd half','31/12/2015')) as TargetDate
From filename;
Let me know if this helps.
Thanks,
Akash.
Also you can use
If (Month(Date)='Apr','1st Half',
If (Month(Date)='May','1st Half',
If (Month(Date)='Jun','1st Half',
If (Month(Date)='Jul','1st Half',
If (Month(Date)='Aug','1st Half',
'2nd Half'))))) As HalfYearVersion,
Hi, Jim
I'm supposing you want more than simply the year of 2015. Perhaps, you have a spreadsheet with tree columns (Year, Version, TargetDate),
If your original table has the fields Year and Version, you can left join it to the table loaded from the spreadsheet. Any combination of Year/Version not present in the spreadsheet, will result in a null TargetDate. Otherwise, it will contain the appropriate target date
Is this want you need?
Hope this helps you
Eduardo
but the Year, how to write it more dynamically. reason is....
i have 2015 and 2016 files loaded in, and moving on client may have 2017,2018....
so i cannot just hardcode the year as well. however, the 31/8 and 31/12 is fixed, year... has to be dynamic. ..
rgds
jim
Hello Mika,
but the Year, how to write it more dynamically. reason is....
i have 2015 and 2016 files loaded in, and moving on client may have 2017,2018....
so i cannot just hardcode the year as well. however, the 31/8 and 31/12 is fixed, year... has to be dynamic. ..
rgds
jim
You can supply the Year field as a parameter to the MakeDate() function:
if(Version='1st half', MakeDate(Year,8,31), if(Version='2nd half',MakeDate(Year, 12,31))) as TargetDate
-Rob
Hi Ron,
Thank you, that's what i am looking for.
thank you.
Rgds,
Jim
thanks shah!