how to add extra field(applymap ?) to existing data

[jim_chan]

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

[robert_mika]

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?

[akash3191]

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.

[prabiradhikary]

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,

[eduardo_sommer]

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

[jim_chan]

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

[jim_chan]

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

[rwunderlich · Accepted solution]

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

http://masterssummit.com

http://qlikviewcookbook.com

[jim_chan]

Hi Ron,

Thank you, that's what i am looking for.

thank you.

Rgds,

Jim

[jim_chan]

thanks shah!