topic Re: Issue with with Pivot table dimenion in QlikView

Issue with with Pivot table dimenion

AC — Tue, 21 Aug 2018 13:40:42 GMT

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.

Re: Issue with with Pivot table dimenion

wdchristensen — Tue, 21 Aug 2018 13:51:03 GMT

=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')))

Re: Issue with with Pivot table dimenion

d_prashanthredd — Tue, 21 Aug 2018 14:02:35 GMT

Hi Anil,

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

Thanks,

Prashanth Reddy D.

Re: Issue with with Pivot table dimenion

neelamsaroha157 — Tue, 21 Aug 2018 14:04:23 GMT

=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

)

))

Re: Issue with with Pivot table dimenion

wdchristensen — Tue, 21 Aug 2018 14:06:24 GMT

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.

Re: Issue with with Pivot table dimenion

wdchristensen — Tue, 21 Aug 2018 14:29:44 GMT

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)))

Re: Issue with with Pivot table dimenion

AC — Tue, 21 Aug 2018 14:54:23 GMT

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)

Re: Issue with with Pivot table dimenion

wdchristensen — Tue, 21 Aug 2018 16:13:38 GMT

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.

Re: Issue with with Pivot table dimenion

AC — Tue, 21 Aug 2018 18:33:16 GMT

Hello William, Its not working by your formula

Re: Issue with with Pivot table dimenion

wdchristensen — Tue, 21 Aug 2018 19:38:13 GMT

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

Re: Issue with with Pivot table dimenion

AC — Tue, 21 Aug 2018 20:05:48 GMT

This is also not working.

Interesting thing is I just tried with some sample data and its working fine with simple If match but the same is not working in my actual application. Please see attached.

Below is the expression which I am using in my actual application and and it says expression is OK

and find the attached sample qvw as well.

Re: Issue with with Pivot table dimenion

wdchristensen — Tue, 21 Aug 2018 20:17:41 GMT

Perhaps Onboard.LAST_PING_STATUS is returning a character ('1','2','3'...) and not an integer (1,2,3...). Maybe try:

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

Re: Issue with with Pivot table dimenion

neelamsaroha157 — Tue, 21 Aug 2018 20:20:50 GMT

The expression looks correct and should work but if its not working for some reason then may be you can pull the numeric part of the Bucket and then instead of using 'match ' in calculated dimension, you can use NumnericBucketField is <=7 in the expression...just an idea!

Re: Issue with with Pivot table dimenion

AC — Tue, 21 Aug 2018 20:25:14 GMT

This expression also says OK in expression editor but nothing is coming up in the table.

Re: Issue with with Pivot table dimenion

AC — Tue, 21 Aug 2018 20:36:05 GMT

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')

The above expression gives the count for 1-7 DAYS and NOT 1-7 DAYS count , this is not what i am looking I want the counts for each individual day like

1 DAY,

2 DAYS,

3 DAYS.

........

7 DAYS

Re: Issue with with Pivot table dimenion

wdchristensen — Tue, 21 Aug 2018 20:37:08 GMT

Maybe:

=if(match(num(Keepchar(Onboard.LAST_PING_STATUS, '0123456789')),'1 DAY','2 DAYS','3 DAYS','4 DAYS','5 DAYS','6 DAYS','7 DAYS'), Onboarad.ReportingBucket)

Re: Issue with with Pivot table dimenion

wdchristensen — Tue, 21 Aug 2018 20:43:41 GMT

If make a table of the source data and the desired response and I can help with the formula.

Example:

Source (ReportingBucket) | Desired Result

1 | 1 Day

2 | 2 Days

NULL | NEVER

Re: Issue with with Pivot table dimenion

wdchristensen — Tue, 21 Aug 2018 20:59:32 GMT

IF(Num(Onboard.LAST_PING_STATUS)=1, Text(Onboard.LAST_PING_STATUS) & ' Day',

IF(Num(Onboard.LAST_PING_STATUS)>1, Text(Onboard.LAST_PING_STATUS) & ' Days', Text(Onboard.LAST_PING_STATUS)))

Re: Issue with with Pivot table dimenion

AC — Wed, 22 Aug 2018 17:49:22 GMT

William, still not able to find the solution for this.

I have created another thread for this issue, can you please check

Issue with if Match function in Straight Table Dimension