Dual(subfield())

[tracycrown]

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

[MK_QSL]

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

[MK_QSL]

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.. !

[tracycrown]

Dear Manish

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

Tracy

[jagan]

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.

[MK_QSL · Accepted solution]

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