Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
jim_chan
Contributor III

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

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

1 Solution

Accepted Solutions

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

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

9 Replies

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

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
New Contributor III

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

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
New Contributor III

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

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
Valued Contributor

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

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
Contributor III

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

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
Contributor III

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

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

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

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
Contributor III

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

Hi Ron,

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

thank you.

Rgds,

Jim

jim_chan
Contributor III

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

thanks shah!

Community Browser