Solved: Reverse references - Qlik Community

Report Inappropriate Content · ‎2015-12-14

Hi, i have a table with items transfers between docks. This looks like:

ITEM	FROM	TO	TRANSFER_ID
PRODUCT 1	DOCK1	DOCK2	A001
PRODUCT 2	DOCK2	DOCK3	A002
PRODUCT 3	DOCK2	DOCK3	B003
PRODUCT 2	DOCK3	DOCK1	A003
PRODUCT 1	DOCK2	DOCK1	B004
PRODUCT 3	DOCK3	DOCK1	A004
PRODUCT 1	DOCK1	DOCK3	B005

I need make a pivot table to show the number of transfers between docks - this is easy - and change the color of cross transfers product - this is a product moved from dock A to dock B and moved from dock B to dock A. The result pivot table may look like:

	DOCK1		DOCK2		DOCK3
ITEM	DOCK2	DOCK3	DOCK1	DOCK3	DOCK1	DOCK2
PRODUCT 1	1	1	1
PRODUCT 2				1	1
PRODUCT 3				1	1

I try with make variables to check the cross transfers:

vDockFrom = Only(FROM);

vDockTo = Only(TO);

=IF (COUNT ({$< FROM={'$(#=vDockTo)'}, TO={'$(#=vDockFrom)'} >} DISTINCT TRANSFER_ID)>0, RGB(255,204,0) )

but this don't work.

Anyone can help me?

Report Inappropriate Content · ‎2015-12-15

I make this solution to extend the graph with interval time.

Extend the original table with a "code" to represent the relation between docks. I.E. Dock A to Dock B and Dock B to Dock A is coded "TA_B" in a new field.

'T'& RangeMinString( right(FROM,1), right(TO,1) ) &'_'& RangeMaxString( right(FROM,1), right(TO,1) ) AS CODE,

ITEM	FROM	TO	TRANSFER_ID	CODE
PRODUCT 1	DOCK1	DOCK2	A001	T1_2
PRODUCT 2	DOCK2	DOCK3	A002	T2_3
PRODUCT 3	DOCK2	DOCK3	B003	T2_3
PRODUCT 2	DOCK3	DOCK1	A003	T1_3
PRODUCT 1	DOCK2	DOCK1	B004	T1_2
PRODUCT 3	DOCK3	DOCK1	A004	T1_3
PRODUCT 1	DOCK1	DOCK3	B005	T1_3

And the Pivot Table Graph is:

	FROM	DOCK1		DOCK2		DOCK3
	TO	DOCK2	DOCK3	DOCK1	DOCK3	DOCK1	DOCK2
ITEM	CODE	T1_2	T1_3	T1_2	T2_3	T1_3	T2_3
PRODUCT 1		1	1	1
PRODUCT 2					1	1
PRODUCT 3					1	1

Expresion:

=COUNT(DISTINCT TRANSFER_ID)

BackGround:

= IF (COUNT(DISTINCT TRANSFER_ID)<>COUNT(DISTINCT TOTAL <ITEM,CODE> TRANSFER_ID), RGB(255,205,0) )

With this solution i can show cross transfers products for a interval date of transactions.

Thanks to jpe‌ to light the path.

View solution in original post

JonnyPoole · ‎2015-12-14

Here is an example.

sunny_talwar · ‎2015-12-14

I think it is the color that isn't working for him Jonathan

sunny_talwar · ‎2015-12-14

Try this as your background color expression:

If(((FROM = 'DOCK1' and TO = 'DOCK2') OR (FROM = 'DOCK2' and TO = 'DOCK1')) and

(Count(DISTINCT TRANSFER_ID)) > 0, RGB(255,204,0))

Report Inappropriate Content · ‎2015-12-14

Hi, thanks but, my problem is the color of cell.

Report Inappropriate Content · ‎2015-12-14

I need make work this solution for "N" docks. Because we have a variable number of docks and i need detect all cross reverse transfers.

sunny_talwar · ‎2015-12-14

Not really sure what you mean. Do you mean something like this?

DOCK1 -> DOCK2 and DOCK2 -> DOCK1 (Color1)

DOCK1 -> DOCK3 and DOCK3 -> DOCK1 (Color2)

DOCK2 -> DOCK3 and DOCK3 -> DOCK2 (Color3)

and so on...

JonnyPoole · ‎2015-12-14

Oh ya.. the whole 'hard part' thing .

Here is a data model solution

JonnyPoole · ‎2015-12-14

Which sets it up for the UI color check

Report Inappropriate Content · ‎2015-12-14

Think in 3 docks today and 10 docks tomorrow and all docks can transfer items to other docks.