topic Re: Fields in Combination in QlikView

Fields in Combination

Wed, 25 Mar 2015 14:23:58 GMT

Hi everyone,

First, I just want to say thank you - I've been posting many questions recently, and I appreciate all your responses.

I have an attached an app for you to look at - here is my problem:

I have Orders that are assigned to a pair of Company1 and Company2. My client is interested in searching for a company and seeing all the Orders and Amounts, whether that company is in Company1 or Company2. So for example in this app, if the client wanted to look at company 'A', he would get Orders u,v,x,y.

In the app, you'll see that I found a way to do this for the table, by using P() in sum(Amounts). However, the problem with this method is that if I select 'A' in Company1 to start, although the table is correct, the Orders list box filters out Order x, which has A in Company2. I want x to be available for further analysis.

How do I create a list box that will allow me to select a company if it is either in Company1 or Company2, and therefore bring up all Orders and sum all Amounts?

Perhaps I need to create a new column with a list of all the companies I want to search? I do not have to use the P() expression - whatever works. Thank you again in advance for your time.

Re: Fields in Combination

MK_QSL — Wed, 25 Mar 2015 14:29:14 GMT

is this right ?

'A', he would get Orders u,v,x,y.

Re: Fields in Combination

Wed, 25 Mar 2015 14:31:57 GMT

Sorry, I updated the app before I attached -

He/she would get t, u, v, x, y.

Re: Fields in Combination

MK_QSL — Wed, 25 Mar 2015 16:31:14 GMT

Use below script

Data:

LOAD * INLINE [

Order, Company1, Company2, Amounts

t, B, A, 7

u, A, G, 15

v, A, A, 10

v, A, B, 20

w, B, C, 2

w, B, D, 4

x, C, A, 10

y, A, A, 5

z, D, F, 10

];

Final:

Load Order, Company1 as Company, 1 as Flag, Amounts Resident Data;

Load Order, Company2 as Company, 2 as Flag, Amounts Resident Data;

Drop Table Data;

Now use below expression

=Concat({<Company = {'$(=GetFieldSelections(Company))'}>}Distinct Order,' ,')

Re: Fields in Combination

maxgro — Wed, 25 Mar 2015 20:10:05 GMT

Data:

LOAD rowno() as id, * INLINE [

Order, Company1, Company2, Amounts

t, B, A, 7

u, A, G, 15

v, A, A, 10

v, A, B, 20

w, B, C, 2

w, B, D, 4

x, C, A, 10

y, A, A, 5

z, D, F, 10

];

Data2:

load id, Company1 as Company, 1 as Type Resident Data;

load id, Company2 as Company, 2 as Type Resident Data;

Re: Fields in Combination

Wed, 25 Mar 2015 21:21:14 GMT

Boom! Thanks guys. Have to give Massimo the "Correct" because I get to keep Company1 and Company2 with his method...