Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Help writing a sort function in an expression

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

Ex..png

13 Replies
jerem1234
Valued Contributor II

Re: Help writing a sort function in an expression

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!

Not applicable

Re: Help writing a sort function in an expression

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

jerem1234
Valued Contributor II

Re: Help writing a sort function in an expression

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.

Not applicable

Re: Help writing a sort function in an expression

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

jerem1234
Valued Contributor II

Re: Help writing a sort function in an expression

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

MVP
MVP

Re: Help writing a sort function in an expression

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)

Not applicable

Re: Help writing a sort function in an expression

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.

excel.png

Not applicable

Re: Help writing a sort function in an expression

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

jerem1234
Valued Contributor II

Re: Help writing a sort function in an expression

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!