Subfield question

[tracycrown]

Dear Sir/Madam

Can someone help me on this requirement :

How to group data in following order with 3 decimals and without negative sign ?

0.001 - 0.020
0.021 - 0.040
0.041 - 0.060
etc

Thank You

Tracy

[arulsettu]

what is the output you are expecting? and can you explain bit more

[tracycrown]

Dear Arul Settu

Thanks for your quick response.

The purpose is to count number of products within the price range.

Example :

Price Range     Numbers of Product

0.001 - 0.020     5

0.021 - 0.040     15

etc

Tracy

[#4]

Hi Tracy,

Find the attached qvw.

Hope it helps.

Regards,

Jemimah

[tracycrown]

Dear Jemimah Bag

Your script is difficult to understand.

Can subfield function be used ?.

Tracy

[jagan]

Hi,

Check this script

LOAD

*,

Num(Trim(SubField([Unit PriceB], ' <= - < ', 1)), '##0.000') & ' ' & Num(Trim(SubField([Unit PriceB], ' <= - < ', 2)), '##0.000') AS UnitPrice_Formatted;

LOAD Product,

     [Unit Price],

    Class([Unit Price],0.02,'-',1) as [Unit PriceB]

FROM

[Test-1.xlsx]

(ooxml, embedded labels, table is Financials);

Regards,

jagan.

[#7]

Hi Tracy,

To achieve your requirement we need to do some data modeling.

Steps In my code:

1.get the max value of unit price.

2. auto generate the from and to fields .

3.Then link them with the original table to find which unit price belongs to which interval.

May be some one else can help you to achieve the same using subfield.

[Kushal_Chawda · Accepted solution]

Please see the attached

[tracycrown]

Dear Kush

Thank you so much, your method is simple and easy to understand.

Can you advise why your method does not work in another set of data, see attached files.

Tracy

[Kushal_Chawda]

Hi,

You have negative values in your data so there could be two ways

If you need negative values ( which is not actually negative) then do as below

LOAD Product,

     [Unit Profit],

     Class(fabs([Unit Profit]),0.020,'',0.001) as [Unit ProfitA],

     replace(Class(fabs([Unit Profit]),0.250,'',0.000),' <=  <','-') as [Unit ProfitB]

FROM

(ooxml, embedded labels, table is Financials);

If you don't need negative values(which is actually negative) then do as below

LOAD Product,

     [Unit Profit],

     Class([Unit Profit],0.020,'',0.001) as [Unit ProfitA],

     replace(Class([Unit Profit],0.250,'',0.000),' <=  <','-') as [Unit ProfitB]

FROM

(ooxml, embedded labels, table is Financials)

where [Unit Profit]>0;