topic Re: Help writing a sort function in an expression in QlikView

Help writing a sort function in an expression

Fri, 15 Jul 2016 13:58:55 GMT

Hello,

I am trying to create a dashboard which takes quantities from a SQL file, divides them by quantities in an Excel file, and will display the percentages accordingly.

In the SQL file, the quantities are divided up by a column with a certain code. If this code matches the code in the Excel file, it should divide them.

For example, lets say the first red column shown below has a code of '1', the yellow has a code of '2', green of '3'. etc. I want the expression within this chart for the red column to display only the SQL data which has a matching code of '1', the yellow column to

display only the SQL data that has a code of '2', the green for '3', etc. Can someone please help me correctly write out these expression statements? So far, I have =Sum(QuantityInSQL)/Sum (QuantityInExcel) , but I need to make it something like =While(Code = '1', Sum(QuantityInSQL)/Sum (QuantityInExcel)) for the red column, =While(Code = '2', Sum(QuantityInSQL)/Sum (QuantityInExcel)) for the yellow column, etc.

Obviously the While() statement doesn't work in Qlikview, and I can't figure out how to do it in a way that Qlikview will understand.

Any help is greatly appreciated.

Thank you,

Alec

Re: Help writing a sort function in an expression

jerem1234 — Fri, 15 Jul 2016 15:29:40 GMT

Can you use a simple if statement like:

if(ExcelCode = SQLCode, Sum(QuantityInSQL)/Sum (QuantityInExcel))

Can you give a small mock-up of what your data looks like? Maybe some stuff can be done easier in loadscript, then used in the dashboard.

Hope this helps!

Re: Help writing a sort function in an expression

Fri, 15 Jul 2016 15:39:59 GMT

I can't use an If() statement because that is dependent on a selection. I need the red column to always display only the data where code = '1', regardless of other selections.

I'm hoping there is someway to do a While() statement or something that functions just like it with Qlikview.

In terms of supplying a small mockup of my data, what would you like to see?

Thanks,

Alec

Re: Help writing a sort function in an expression

jerem1234 — Fri, 15 Jul 2016 15:55:34 GMT

Are your fields for the SQL Data and Excel Data linked in the data model or are they separated?

As for mockup data, I'm just looking for what your data model looks like.

If you can provide made up data with like 5 rows of data for your SQL and Excel tables similar to something like:

SQLCode, QuantityInSQL

5, 45

4, 60

3, 70

and then do an ExcelCode table, and then say whether or not they are linked or there is actually only one table.

This will help deduce whether to combat the problem in the loadscript or with an expression in the dashboard.

Re: Help writing a sort function in an expression

Fri, 15 Jul 2016 16:37:35 GMT

Certainly.

For example, I have

SQLCode, QuantityInSQL

1, 2

1, 5

1, 3

1, 7

2, 2

2, 7

2, 4

2, 3

I would need my charts to say the red column (code 1) has a quantity of 17, the yellow column (code 2) has a quantity of 16, etc.

and they are linked.

I still need all of the data in the loadscript, because I am also combining these QuantitiesInSQL together, ignoring the SQLCode, in a different chart, contained in the same container. For this chart, I need it to be separated by SQLCode. I hope this makes sense, I can definitely clarify if it is confusing.

Thanks,

Alec

Re: Help writing a sort function in an expression

jerem1234 — Fri, 15 Jul 2016 17:32:43 GMT

What table has QuanitityInExcel? Can you show what that table might look like and how its linked to the QuantityinSQL table?

Re: Help writing a sort function in an expression

maxgro — Fri, 15 Jul 2016 17:51:04 GMT

did you try to rename the excel code field and the sql code filed with the same name?

or could you post your model (ctrl +t, table viewer)

Re: Help writing a sort function in an expression

Fri, 15 Jul 2016 19:06:58 GMT

It's linked up inside of Qlikview, I have posted a picture below for reference. The smaller table on the left is from SQL, the larger one on the right is from Excel. I have also placed a picture of the excel table below that. These are the quantities for different locations, and each row corresponds to a different part number.

Re: Help writing a sort function in an expression

Fri, 15 Jul 2016 19:07:53 GMT

Here is the model, and I do have one field (PartMPN) which is named the same name in both the SQL and the Excel table so it is the PK

Re: Help writing a sort function in an expression

jerem1234 — Fri, 15 Jul 2016 19:24:31 GMT

So is it the case you want the table to show no matter what selections you have?

Try adding set analysis in:

Sum({1}QuantityInSQL)/Sum ({1}QuantityInExcel)

{1} will ignore all the selections you have.

If you need to use the solution I listed above while ignoring selections try doing:

if(only({1}SQLCode) = only({1}ExcelCode), Sum({1}QuantityInSQL)/Sum ({1}QuantityInExcel))

Let me know if this helps or I am misunderstanding your requirements!

Re: Help writing a sort function in an expression

Wed, 20 Jul 2016 13:14:52 GMT

Hi Jeremiah,

I apologize for my late reply, I have been out of the office for the last several days.

I want it to show no matter what the selections are. It will always be populated with data featuring different codes, and I need it to display each code as a separate bar on the bar graph. I believe your solution above will be helpful, I am going to try it and will update with results. In addition, if anything I've said here helps to clarify what I am looking for, please feel free to let me know any ideas you may have.

Thanks,

Alec

Re: Help writing a sort function in an expression

Thu, 21 Jul 2016 15:42:29 GMT

Hi Jeremiah,

I have tried your solution and found you are understanding exactly what I am looking for. One small issue though, with the code:

if(only({1}SQLCode) = only({1}ExcelCode), Sum({1}QuantityInSQL)/Sum ({1}QuantityInExcel))

I want it to say something more like this:

if(only({1}SQLCode) = '1', Sum({1}QuantityInSQL)/Sum ({1}QuantityInExcel))

The problem I am having is that it is saying there is no data to display when I do it like this. I have also tried:

if(only({1}SQLCode) = '1', Sum(QuantityInSQL)/Sum (QuantityInExcel))

and

if(SQLCode = '1', Sum({1}QuantityInSQL)/Sum ({1}QuantityInExcel))

and I'm having no luck. Do you know what I can do to make it work?

Thank you so much for your help, I really do appreciate it.

Alec

Re: Help writing a sort function in an expression

Thu, 21 Jul 2016 18:38:47 GMT

Hi alec,

consider loading the excel data using cross-table statement.

Yair.

Re: Help writing a sort function in an expression

jerem1234 — Thu, 18 Aug 2016 14:50:24 GMT

Sorry for getting back so late, but I am not quite sure what you are seeking. For your purpose, I would use the SQL Code as a dimension and then use:

Sum({1}QuantityInSQL)/Sum ({1}QuantityInExcel)

as the expression in the bar chart.