Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Issue with with Pivot table dimenion

Hello,

    I am trying to create a new  field in Pivot table dimension from an existing field, I have tried using if match and nested if as well but nothing is working not sure what the problem is, here is what I am trying to do.

I have a LastReportedBucket filed with the values (1 DAY, 2-7 DAYS, 8-30 DAYS, 31+ DAYS and Never) from this I want to create a filed in Pivot table dimension like below.

1 DAY and 2-7 DAYS to 1-7 DAYS

8-30 DAYS and 31+ DAYS to 7+ DAYS

Nerver to Nerver


I was trying below expression and its not showing any error in the expression window but I am not getting anything in the Pivot table


=if(Match(LastReportedBucket, '[1 DAY]','[2-7 DAYS]'),'[1-7 DAYS]',

if(Match(LastReportedBucket, '[8-30 DAYS]','[31+ DAYS]'), '[7+ DAYS]',

if(Match(LastReportedBucket,'NEVER','NEVER'),LastReportedBucket)))


I have similar issue with another field called ReportingBucket and the the values are 1 DAY, 2 DAYS, 3 DAYS.....30 DAYS from this i was trying to display first 7 DAYS (1 DAY, 2 DAYS...7 DAYS) using if match here also same issue.


1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

I like the way Neelam coded his "no match condition" because I am not typically the data owner and in my experience data will commonly change without anyone notifying me. So in 6 months when someone adds a new category '0 SAME DAY', I don't want those records falling in the 'UNCATEGORIZED WARNING' category as I previously coded. In that scenario, any new category will require a code change. However, if you coded the last "if statement" with a reference back to the original category, you might get lucky and not have to touch the code. In that example, if they wanted same day to be in it's own category then no change is needed.

=if(LastReportedBucket='1 DAY' OR LastReportedBucket='2-7 DAYS', '1-7 DAYS',

     if(LastReportedBucket='8-30 DAYS' OR LastReportedBucket='31+ DAYS', '7+ DAYS',

          if(LastReportedBucket='NEVER', 'NEVER', LastReportedBucket)))

View solution in original post

18 Replies
Highlighted
Specialist
Specialist

=if(LastReportedBucket='[1 DAY]' OR LastReportedBucket='[2-7 DAYS]', '[1-7 DAYS]',

     if(LastReportedBucket='[8-30 DAYS]' OR LastReportedBucket='[31+ DAYS]', '[7+ DAYS]',

          if(LastReportedBucket='NEVER', 'NEVER', 'UNCATEGORIZED WARNING')))

Highlighted
Creator III
Creator III

Hi Anil,

Why you need square ([ ]) brackets in single quotes? Ignore them and check it.

Thanks,

Prashanth Reddy D.

Highlighted
Specialist II
Specialist II

=if(Match(LastReportedBucket, '[1 DAY]','[2-7 DAYS]'),'[1-7 DAYS]',

if(Match(LastReportedBucket, '[8-30 DAYS]','[31+ DAYS]'), '[7+ DAYS]',

if(Match(LastReportedBucket,'NEVER'),'NEVER',LastReportedBucket

)

))


Highlighted
Specialist
Specialist

It is much easier to code solution when actual sample data is provided... I agree that you don't need brackets unless they are present in your actual data.

Highlighted
Specialist
Specialist

I like the way Neelam coded his "no match condition" because I am not typically the data owner and in my experience data will commonly change without anyone notifying me. So in 6 months when someone adds a new category '0 SAME DAY', I don't want those records falling in the 'UNCATEGORIZED WARNING' category as I previously coded. In that scenario, any new category will require a code change. However, if you coded the last "if statement" with a reference back to the original category, you might get lucky and not have to touch the code. In that example, if they wanted same day to be in it's own category then no change is needed.

=if(LastReportedBucket='1 DAY' OR LastReportedBucket='2-7 DAYS', '1-7 DAYS',

     if(LastReportedBucket='8-30 DAYS' OR LastReportedBucket='31+ DAYS', '7+ DAYS',

          if(LastReportedBucket='NEVER', 'NEVER', LastReportedBucket)))

View solution in original post

Highlighted
Contributor II
Contributor II

Thanks William, this works fine and it is working with IF clause not with if match

I have another field called ReportingBucket and the the values are 1 DAY, 2 DAYS, 3 DAYS.....30 DAYS from this i was trying to display first 7 DAYS (1 DAY, 2 DAYS...7 DAYS) and am using like below


=if(match(Onboarad.ReportingBucket,'1 DAY','2 DAYS','3 DAYS','4 DAYS','5 DAYS','6 DAYS','7 DAYS'),Onboarad.ReportingBucket)

Highlighted
Specialist
Specialist

What I think you are wanting to do is check to see if the ReportingBucket is between 1 DAY and 7 DAYS. However you can't just do a numerical check but you have a field that contains string values. If we removed " DAY", " DAYS" and "NEVER" we could cast the remaining characters to an integer. So 7 DAYS would return the number 7 with the formula below:


Num(Replace(Replace(ReportingBucket,' DAYS',''),' DAY',''), '0')


So you could use the test condition 1-7 as follows with an if statement.


IF(Num(Replace(Replace(Replace(ReportingBucket,'NEVER’, '-1'),' DAYS',''),' DAY',''), '0') > 0

AND Num(Replace(Replace(Replace(ReportingBucket,'NEVER’, '-1'),' DAYS',''),' DAY',''), '0') <= 7, '1-7 DAYS', 'NOT 1-7 DAYS')


As a personal preference I don't like these types my formulas in the application. I typically do this transformation in my load scripts so if I need to have a numeric field for my IF statement, I just create a new field but it will work either way.

Highlighted
Contributor II
Contributor II

Hello William, Its not working by your formula

Highlighted
Specialist
Specialist

Try:

IF(Num (Replace (Replace (Replace (ReportingBucket,'NEVER', '-1'),' DAYS',''),' DAY',''), '0') > 0

AND Num(Replace(Replace(Replace(ReportingBucket,'NEVER', '-1'),' DAYS',''),' DAY',''), '0') <= 7, '1-7 DAYS', 'NOT 1-7 DAYS')

Some of my single quotes were from MS Word and that can cause issues...

If the above formula doesn't work please let me know exactly what error / response you get. Does the logical process make sense to you (converting string to numeric)? Thanks