topic Re: Reassign Accounts in one Month to Another Month in QlikView

Reassign data in one Month to Another

MalcolmCICWF — Wed, 27 Feb 2013 18:43:36 GMT

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

Re: Reassign Accounts in one Month to Another Month

MayilVahanan — Wed, 27 Feb 2013 18:56:07 GMT

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

Re: Reassign Accounts in one Month to Another Month

MalcolmCICWF — Thu, 28 Feb 2013 20:19:01 GMT

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?

Re: Reassign data in one Month to Another

Thu, 28 Feb 2013 21:10:27 GMT

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.

Re: Reassign data in one Month to Another

MalcolmCICWF — Fri, 01 Mar 2013 14:51:59 GMT

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.