Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MalcolmCICWF
Creator III
Creator III

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
MayilVahanan

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
MalcolmCICWF
Creator III
Creator III
Author

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

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
Creator III
Creator III
Author

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.