topic Re: Dual(subfield()) in QlikView

Dual(subfield())

tracycrown — Tue, 23 Dec 2014 23:56:51 GMT

Dear all

Merry Christmas

I am unable to create price range for 1-5 (ie 4.999), 5-10, 10-15 etc. Can someone help to explain the logic works on following script ?

Dual(SubField(Class(Price,4,'',1),' <= < ',1)&' - '&(SubField(Class(Price,5,'',1),' <= < ',2) -1), Class(Price,5,'',1)) as [Price Group],

Thank you so much

Tracy

Re: Dual(subfield())

MK_QSL — Wed, 24 Dec 2014 00:01:05 GMT

Provide sample data along with the result you are looking for... would help you on this...

Re: Dual(subfield())

MK_QSL — Wed, 24 Dec 2014 00:11:27 GMT

OK... let me try without your reply or data..

Use below function to create 0-5, 5-10, 10-15.... ranges

Dual(Replace(Class(Price,5),'<= x <','-'),Class(Price,5)) as [Price Group]

Class function is giving the final data range in numeric format..

Using Class(Price,5), you will get result something like

0<= x <5

5<=x <10

etc

but if you want to replace '<= x <' with '-', we have to use replace function.

But replace function will give your the final result in text format, so if you try to sort your final chart based on Price Group field, you will get sorting something 0-5, 10-15, 20-25 etc.. (i.e. sort by text)

Dual function will store the data both Text and Number format... Dual(Text,Number), so you can sort by numeric value also..

Hope this would help..

Happy Qliking.. !

Re: Dual(subfield())

tracycrown — Wed, 24 Dec 2014 01:10:11 GMT

Dear Manish

Thank you for your quick response, please see attached data file.

Tracy

Re: Dual(subfield())

jagan — Wed, 24 Dec 2014 01:51:01 GMT

Hi Tracy,

Please find below script for solution

Using Class

LOAD Item,

Dual(Replace(Class([Unit Price],5),'<= x <','-'),[Unit Price]) as [Price Group],

[Unit Price]

FROM

(biff, embedded labels, table is POS$);

Using Floor & Ceil functions

LOAD Item,

Dual(Floor([Unit Price], 5) & '-' & Ceil([Unit Price], 5), [Unit Price]) AS [Price Group],

[Unit Price]

FROM

(biff, embedded labels, table is POS$);

Regards,

Jagan.

Re: Dual(subfield())

MK_QSL — Wed, 24 Dec 2014 07:42:45 GMT

use the code provided in my previous reply. It should work.