topic Re: Subfield question in QlikView

Subfield question

tracycrown — Fri, 19 Jun 2015 03:28:40 GMT

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

Re: Subfield question

arulsettu — Fri, 19 Jun 2015 03:38:41 GMT

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

Re: Subfield question

tracycrown — Fri, 19 Jun 2015 04:28:13 GMT

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

Re: Subfield question

Fri, 19 Jun 2015 05:08:54 GMT

Hi Tracy,

Find the attached qvw.

Hope it helps.

Regards,

Jemimah

Re: Subfield question

tracycrown — Fri, 19 Jun 2015 08:55:46 GMT

Dear Jemimah Bag

Your script is difficult to understand.

Can subfield function be used ?.

Tracy

Re: Subfield question

jagan — Fri, 19 Jun 2015 09:05:12 GMT

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.

Re: Subfield question

Fri, 19 Jun 2015 09:11:21 GMT

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.

Re: Subfield question

Kushal_Chawda — Fri, 19 Jun 2015 09:23:52 GMT

Please see the attached

Re: Subfield question

tracycrown — Fri, 19 Jun 2015 14:11:47 GMT

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

Re: Subfield question

Kushal_Chawda — Fri, 19 Jun 2015 17:29:49 GMT

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;

Re: Subfield question

tracycrown — Sat, 20 Jun 2015 07:21:13 GMT

Dear Kush

Thanks for your good advice.

Your solution gives correct answer but how to show three decimals for all numbers ?.

For example :

0.000 - 0.250

0.250 - 0.500

etc

Thank You

Tracy

Re: Subfield question

Kushal_Chawda — Sat, 20 Jun 2015 07:58:23 GMT

Hi,

Please find the attached file. I am getting values for 3 decimals points

Re: Subfield question

tracycrown — Sat, 20 Jun 2015 09:05:51 GMT

Dear Kush

Sorry, there is no file attached for [Unit ProfitB].

Can you also advise what is the purpose of [Unit ProfitA] ?

Thank you so much

Tracy

Re: Subfield question

Kushal_Chawda — Sat, 20 Jun 2015 15:06:38 GMT

Hi Tracy,

[Unit ProfitA] is just for sorting purpose. See the sort tab in chart properties.

Attached file again

Re: Subfield question

tracycrown — Sun, 21 Jun 2015 10:11:17 GMT

Dear Kush

Thanks for your attachment for Unit Profit,

please advise the following :

1. What is the purpose of fabs function ?..

2. How to get 3 decimals if I can 0.001 to 0.000 ?

3. For Q2, how to use num(Trim(SubField([Unit ProfitB], ' <= - < ', 1)), '##0.000') ?

4. Your script produces following results :

0.001 - 0.021

0.021 - 0.041 ..

But how to modify the script to show following format :

0.000 - 0.020

0.021 - 0.040

Thank you so much for your kind advice.

Tracy

Re: Subfield question

pratap6699 — Sun, 21 Jun 2015 19:12:48 GMT

use class and replace functions in script

Re: Subfield question

tracycrown — Sun, 21 Jun 2015 22:14:14 GMT

Dear Pratap Budati

Please show me an example based on Test-2.xls.

Thanks

Tracy