Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Class,Replace,Dual Functions

bharat_kethired
New Contributor II

Class,Replace,Dual Functions

Hi,

     I would like to discuss about CLASS,REPLACE,DUAL functions in qlikview. I will explain these functions by taking a sample business scenario.

         

upload.PNG

U Can observe that for different Sale Id we have different Sale Amount. Now, Our requirement is that our organization wants to organize all the sales based on range.

  Desired output

upload1.PNG

Follow , the below steps inorder to obtain the desired output.

  • Right click-->NewSheetObject-->Straight Table
  • In the Dimensions tab,U hav to add calculated Dimension

     Class.PNG

CLASS Function

     CLASS1.PNG

syntax:- Class([Desired field],range)

Class3.PNG

  • After adding the Calculated Dimension,click Next
  • Add Expression as count[SaleId]

The output obtained is,

     Main class.PNG

Now, if you want to remove '<= x <' symbols in between the numbers, u can use replace function.

Replace 

Edit the calculated dimension as -->

     Replace.PNG

Syntax: Replace([field],'what you want to replace','what should be replaced')

Output :

Replace result.PNG

U can , now observe that '<=  x <' is replaced with '-'.

However, you can see that range values are not sorted correctly . To, rearrange them in a proper order with equi range distribution You can use DUAL function.

DUAL

Edit the calculated dimension as -->

Dual.PNG

Output:

     Dual Result.PNG

You can now observe that Sales range is ordered accordingly and Count of sales can be observed easily by seeing the above table.

Final Output:-

Final output.PNG

Note: I dn't want to see those sales which are not in range. so,I have checked, Supress Null Values i.e why u can't see 600-700 ,800-900,.....2200-2300 in Sales range column .

    • Like/Mark my document as HELPFUL if u find it useful for you.


Thanks & Regards,

K.bharat

Tags (3)
Comments
MVP
MVP

Good Information and thanks for this post.

The reason behind sorting issue after using Replace is..

1) Class function will provide output in dual, so you can sort the values according to numeric format.

2) But once you are using Replace function to remove <= x <, the whole expression/calculated dimension converted to string/text format because Replace is a string function and will return Text or String.

3) Using Dual will make the whole expression/calculated dimension back to dual format.


Regards,

MK

Not applicable

Nice Information.

Not applicable

Good information.

zhadrakas
Valued Contributor

good Job. i will definitetly use this

Not applicable

Great.Does it work same way in script as well!!!

oknotsen
Honored Contributor III

I would even advise to mainly / only use these functions in script and not in the front-end. Not doing it in the front-end is way better for performance.

bharat_kethired
New Contributor II

Yes, U can

hemachandran
Contributor

Good article.. its useful for me...

ahaahaaha
Honored Contributor

Thank you for good information.

rahulpawarb
Valued Contributor III

Thank you Bharat! Good Job.

Version history
Revision #:
1 of 1
Last update:
‎01-19-2017 02:19 AM
Updated by: