topic Re: Merging different results into one based on different conditions in QlikView

Merging different results into one based on different conditions

Anonymous — Thu, 27 Jul 2017 05:02:32 GMT

Hi All,

I have data as shown in below. Could some one suggest me to build stack barchart, how to get Derive Line Name and show sum() as shown in result set.

First three columns available in script. How to derive line name as Dimension? Sum of result set shown in last.

Line Name	Family Name	Quantity	Derive Line Name
A Sales	Z	100	A
A Sales	O	90	A
A Sales	S	70	A
A Sales	BF	70	A
A Sales	E	60	A
A Sales OS	E	40	A
A Sales OS	S	70	A
A Sales OS	Z	70	A
A Sales OS	BF	60	A
A Stock Sales	Z	90	A
A Stock Sales	S	70	A
A Stock Sales OS	S	70	A
NONE	O	60	exclude in Chart
NONE	TREATMT	40	exclude in Chart
NONE	GLAZING	100	exclude in Chart
NONE	TINT	90	T
NONE	COAT	70	exclude in Chart
B Sales	Z	70	B
B Sales	BF	60	B
B Sales	S	40	B
B Sales	E	70	B
B Sales OS	BF	60	B
C Sales	BF	40	C
C Sales	S	100	C
C Sales	Z	90	C
C Sales	E	70	C
C Sales OS	S	40	C
C Sales OS	BF	70	C
C Stock Sales	Z	70	C
C Stock Sales	S	60	C
C Stock Sales OS	S	90	C

Result set with expression:

Derived Line Name	Quanity
A	sum(A) - Sum(T)
T	Sum(T)
B	Sum(B)
C	Sum(C)

Please suggest me, is it possible to do with out touching script and just with Dimensions and Expressions?

Thanks to suggest.

Regards,

Krishna

Re: Merging different results into one based on different conditions

techvarun — Thu, 27 Jul 2017 05:05:33 GMT

You have to use ValueList function to achieve the result.

From QlikHelp
http://help.qlik.com/en-US/sense/June2017/Subsystems/Hub/Content/ChartFunctions/SyntheticDimensionFunctions/valuelist.htm

Re: Merging different results into one based on different conditions

techvarun — Thu, 27 Jul 2017 05:14:42 GMT

Please refer the attached

Re: Merging different results into one based on different conditions

Anonymous — Thu, 27 Jul 2017 05:15:30 GMT

Hi Varun,

Could you give me an example with Sample data. I tried to understand from the above mentioned link. But bit confused.

Regards,

Krishna

Re: Merging different results into one based on different conditions

techvarun — Thu, 27 Jul 2017 05:18:46 GMT

Sample qvw posted below. Please check

Re: Merging different results into one based on different conditions

Anonymous — Thu, 27 Jul 2017 09:16:41 GMT

Hi Varun,

Certainly, value list helped to an extent. Is there any possibility that, while sum we can count quantity of other dimension.

For simple data model i directly updated quantity in the morning. but in my application i need to sum/ count the quantity based on other dimension.

Thanks to confirm.

Re: Merging different results into one based on different conditions

Anonymous — Thu, 27 Jul 2017 09:27:12 GMT

Hi Varun,

For better understanding i attached excel sheet with sample table and info as comments. Thanks to advice. !

Re: Merging different results into one based on different conditions

Kushal_Chawda — Thu, 27 Jul 2017 10:14:23 GMT

How would you define line name A,T,B & C also NONE, in your sample data I don't see it

Re: Merging different results into one based on different conditions

Anonymous — Thu, 27 Jul 2017 10:21:22 GMT

Hi Kushal,

Condition as below:

If(Line Name like"A S*", A,If(Line Name like "NONE" and Familyname "TINT", 'T', if(Line Name = "B S*", B, IF(LINE Name like 'C S*', C'))))

Finally Line Name NONE and Family Name <> TINT then it is NONE. Which i dont want to see in char sales.

Re: Merging different results into one based on different conditions

Kushal_Chawda — Thu, 27 Jul 2017 10:42:38 GMT

see the attached

Re: Merging different results into one based on different conditions

Anonymous — Thu, 27 Jul 2017 12:15:38 GMT

Hi Kushal,

In the sample application quantity is part of same dimension. but in my application quantity is coming from facts table and line name , family name are part of product table. Line code is key column between two tables. In this case i am unable to group line name for A,B,C and for line name T it is combination of T and family name. Please suggest me considering quantity is in another table. Even in my sample data i mentioned both as different tables.

Thankyou.

Re: Merging different results into one based on different conditions

Kushal_Chawda — Thu, 27 Jul 2017 12:33:07 GMT

Can you join both the tables? If you can join both the table on DateKey and Line Code ,you can follow my approach

Re: Merging different results into one based on different conditions

Anonymous — Thu, 27 Jul 2017 13:53:10 GMT

Not possible Kushal. We can not join Facts and dimensions.

Re: Merging different results into one based on different conditions

Kushal_Chawda — Fri, 28 Jul 2017 05:02:49 GMT

Any specific reason?

Re: Merging different results into one based on different conditions

Anonymous — Fri, 28 Jul 2017 05:35:43 GMT

There are many other dimensions connected to facts and Management does not accept to merge. Strictly they oppose script level changes.