Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

MalcolmCICWF
Contributor II

Reassign data in one Month to Another

Help me out QV Community, I have some accounts that were placed with us at the beginning of say December that were Supposed to be placed in November. I don't want to manipulate the database because we want to actually know the placed date, yet I want them reported for November. Would I just have to do some sort of renaming of the Month in the script? How would I do this with multiple field criteria like below.

Basically if the field TIER is "Quint" and then DATE_FIELD is "20121201", I want all those December dates to show up as November dates in the DATE_FIELD, but only for that month if it is that TIER.

This is what I was thinking of using but not sure it is what I need

IF(Tier = 'Quint' and DATE_FIELD='20121201',20121130,DATE_FIELD) as DATE_FIELD 

4 Replies

Re: Reassign Accounts in one Month to Another Month

Hi

Are you want to change the date field for december to "20121130" or exactly one month back? for ex: 20121202 goes to 20121102 or 20121130?

IF(Tier = 'Quint' and MonthName(DATE_FIELD) = 'Dec 2012',AddMonths(DATE_FIELD,-1), DATE_FIELD)

//if your date format is not in standard format , use like this

Date(AddMonths(Date(Date#(DATE_FIELD,'YYYYMMDD'),'MM/DD/YYYY'),-1),'YYYYMMDD')

Hope it helps

MalcolmCICWF
Contributor II

Re: Reassign Accounts in one Month to Another Month

If you look at my attachment from the original post, the accounts and data are showing under June, I would like to move them to May (I used Nov and Dec as an example). Basically some sort of IF statement to manipulate my data when I select it. As my question header says, I need to reassign anything under once month/year, under that "Tier" to be categorized into another month.

anyone have any experience with this?

Not applicable

Re: Reassign data in one Month to Another

You could do a case-when statement in the load script.

load *,

case

     when tier='quint' and date_field = 'may 2012' then 'june 2012',

     else date_field

     as date_field2

     end

from ....

I'm not a sql expert, so you might want to look up the syntax to see where the commas go.

MalcolmCICWF
Contributor II

Re: Reassign data in one Month to Another

Thanks, but I would like to keep the script in Qlikview language, as all the script written already is. I pull from various created qvds.

Community Browser