topic Re: Formular sorting one number in QlikView

Formular sorting one number

Thu, 12 Nov 2015 15:03:52 GMT

hey @ all,

i am having a problem with a diagram and the values which shoud be shorted for this diagramm.

The Problem:

I had to make some diagrams from some orderings but the order is the last two numbers from a value in this number.

For example:

Ordernumber1: 100458500030

Ordernumber2: 100898500080

My testing idea was to that there is a condition using the formula like =if(OperationId=(*30),OperationId) but the *30 isn't working.

For this diagram i want to sort after 30 and 80.

All with 30 at the end in one diagramm and all 80 in the the other one.

I didn't find any way to make this working.

And my desire is that i can generate diagrams after every, i will say status(30,80), for this at runtime.

Re: Formular sorting one number

rubenmarin — Thu, 12 Nov 2015 15:55:56 GMT

Hi Tim, I'm not sure to understand the problem but about your 'If' testing, you can try with:

=if(Right(OperationId, 2)='30',OperationId)

Re: Formular sorting one number

MarcoWedel — Sat, 14 Nov 2015 00:32:28 GMT

please post some more sample data and your exprected result.

regards

Marco

Re: Formular sorting one number

Mon, 16 Nov 2015 11:58:19 GMT

@Ruben Marin:

Thx for your request this was the right formula. My solution for the first step now is:

sum(if(Right(OperationId, 2)='30',yield.primary))

yield.primary = the pices per Order

The second problem with more sample data and information:

OrderIDs: department:

Ordernumber1: 100458500030 IT

Ordernumber2: 100898500080 IT

Ordernumber3: 100834300080 production

Ordernumber4: 100789500010 production

Ordernumber5: 100321500050 production

In the table above you can see that there are two orders for IT with two different last numbers: 30 and 80

And for prodction there are 3: 10, 50 and 80

My idea is that i can create diagramms dynamic for each department and the last numbers.

For example: Cost center one hast 2 steps for making a product and cost center two has 3.

If the user is looking at a cost center he wants to see the data for every step but the problem is that there are different steps(numbers) and different numbers in steps.

My question is how can i solve this that qlikview shows two diagramms for IT (30,80) and for production 3 (80,10,50) without looking at 3 diagramms(one empty) in IT.

I hope you can understand my idea.

Thx for all your comments.

Tim

Re: Formular sorting one number

Anonymous — Mon, 16 Nov 2015 12:04:12 GMT

You are missing Like operator with wildmatch string, try this:

=if(OperationId like '*30',OperationId) //will work for sure

Also check the Suppress null values

Re: Formular sorting one number

Kushal_Chawda — Mon, 16 Nov 2015 12:10:04 GMT

Create the two flags in the script

=if(wildmatch(Ordernumber1,'*30',1) as OrderFlag_30,

if(wildmatch(Ordernumber1,'*80',1) as OrderFlag_80

Now you can use this Flag in your expression

{<OrderFlag_30={'1'}>}

Re: Formular sorting one number

Kushal_Chawda — Mon, 16 Nov 2015 12:13:26 GMT

what do you mean by Diagram? Can you draw one Diagram for us?

Re: Formular sorting one number

rubenmarin — Mon, 16 Nov 2015 12:54:15 GMT

I don't know what you mean by 'Diagram' either but if you want to use that 2 last digits as a dimension you may create it as a field:

LOAD ....

Right(OperationId, 2) as StepNumber

To show only by department (without empy 'diagrams') depends of the kind of chart you're using and how distributes the information, maybe you find that issue or maybe it directly works as you want.

Re: Formular sorting one number

Mon, 16 Nov 2015 13:25:12 GMT

This is a sample i made.

Every of this chart should be on the same page.

The left one is cost center X with two operations. op 10 and op 80 should be shown.

The middle is how it looks now. A diagramm with no data in op 30 for cost center Y.

But i only want to look at the right side with the op 10 shown on the page for this costcenter.

Re: Formular sorting one number

rubenmarin — Mon, 16 Nov 2015 13:47:16 GMT

If you create one field to store the operation (or StepNumber) you can use this field as the conditional show of the chart, in the 'Design' tab check the 'Conditional' option and an expression similar to:

=Count({<StepNumber={'30'}, CostCenter={'Y'}>} StepNumber)

If the count is =0 (no records with StepNumber='30' for the CostCenter='Y') the chart will be hidden.

Re: Formular sorting one number

Wed, 18 Nov 2015 08:35:54 GMT

Hey Ruben,

Thx for your answer, i am a newby and can't follow you.

I think there could be a way over one step more:

I made a listbox, where die OrderIDs where sorted like ( 20, 50, 80)

and now i want to read the data form this listbox like

=sum(if(Right(OperationId, 2)='THE_FRIST_NUMBER_ID_IN_THE_LISTBOX',opm.act.yield.primary))

And for this i can make one formula for every diagramm like the followed ones:

Diagramm 1:

=sum(if(Right(OperationId, 2)='THE_FRIST_NUMBER_ID_IN_THE_LISTBOX',opm.act.yield.primary))

Diagramm 2:

=sum(if(Right(OperationId, 2)='THE_SECOND_NUMBER_ID_IN_THE_LISTBOX',opm.act.yield.primary))

For this die diagramms and the listbox is very dynamic for every costcenter because of there are other operationIDs in the listbox for every costcenter.

Thx for your help!

Re: Formular sorting one number

Thu, 19 Nov 2015 08:39:39 GMT

Any idea?

Re: Formular sorting one number

rubenmarin — Thu, 19 Nov 2015 10:22:24 GMT

Hi Tim, sorry about the delay but I'm busy and I can't elaborate any sample.

Just some tips in case those helps you:

- Using "concat(distinct FieldNameInListBox, ';')" or "getfieldselections(FieldNameInListBox, ';')" you can get all the values in the list box separated by a semicolon (you can change the separator). The getfieldselections option only retrieves all selected values, the concat option returns all posible values.

- Based on this string you can use Subfield() to get the value in a determined position, i.e., using Subfield(Concat(distinct FieldNameInListBox, ';'), ';', 2), you'll get the second value of the possible values in the listbox.

So you can try with:

Diagram 1: =sum(if(Right(OperationId, 2)=Subfield(Concat(distinct FieldNameInListBox, ';'), ';', 1),opm.act.yield.primary))

Diagram 2: =sum(if(Right(OperationId, 2)=Subfield(Concat(distinct FieldNameInListBox, ';'), ';', 2),opm.act.yield.primary))

Re: Formular sorting one number

Mon, 23 Nov 2015 12:23:01 GMT

Hello Ruben,

Thx for your Help, i think it's the one i want to have but,

i can't go on because i don't know how qlikview knows which listbox/element it should use:

In the picture you can see the listbox is got after filling in the formula and the frist number in the orange circle (placeholders 1-2-3) i want so see in the chart above. If there is a 50 in the frist row then the chart shows there numbers.

For this your answer should be the right one but how can i use (distinct FieldNameInListBox, to get these numbers?

Qlikview tells me there is an error in the formula.

I think it's a my mistake because of didn't know much about qlikview works.

(The window at the right are the properties form the listbox.

Thanks for your help again.

Re: Formular sorting one number

rubenmarin — Tue, 24 Nov 2015 08:12:08 GMT

Hi Tim, as said above (check the "16-nov-2015 13:54" post) if you create a field in the script it will be easier, as you can use that field name.

Now you have an expression in the list box, not a named field.

Re: Formular sorting one number

Mark_Little — Tue, 24 Nov 2015 08:30:38 GMT

HI,

Why don't you create this in script so you can easily access in the set analysis.

Right(OperationId, 2) as OperationSort,

Then in your set analysis you can just do thing like

Sum({<OperationSort={30}>Value}

Sum({<OperationSort={80}>Value}

Sum({<OperationSort,={30,80}>Value}

Mark