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.


18 Replies
Highlighted
Contributor II
Contributor II

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.

Capture.PNG !

Highlighted
Specialist
Specialist

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)

Highlighted
Specialist II
Specialist II

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!

Highlighted
Contributor II
Contributor II

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

Highlighted
Contributor II
Contributor II

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

Capture1.PNG

Highlighted
Specialist
Specialist

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)

Highlighted
Specialist
Specialist

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

Highlighted
Specialist
Specialist

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

Highlighted
Contributor II
Contributor II

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