topic Reverse references in QlikView

Reverse references

Mon, 14 Dec 2015 19:35:25 GMT

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?

Re: Reverse references

JonnyPoole — Mon, 14 Dec 2015 20:01:24 GMT

Here is an example.

Re: Reverse references

sunny_talwar — Mon, 14 Dec 2015 20:04:57 GMT

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

Re: Reverse references

sunny_talwar — Mon, 14 Dec 2015 20:08:27 GMT

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))

Re: Reverse references

Mon, 14 Dec 2015 20:28:01 GMT

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

Re: Reverse references

Mon, 14 Dec 2015 20:31:45 GMT

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.

Re: Reverse references

sunny_talwar — Mon, 14 Dec 2015 20:40:37 GMT

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...

Re: Reverse references

JonnyPoole — Mon, 14 Dec 2015 21:06:35 GMT

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

Here is a data model solution

Re: Reverse references

JonnyPoole — Mon, 14 Dec 2015 21:06:59 GMT

Which sets it up for the UI color check

Re: Reverse references

Mon, 14 Dec 2015 21:13:11 GMT

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

Re: Reverse references

Mon, 14 Dec 2015 21:47:40 GMT

Thanks, with this now i can extend the solution - with interval dates - and make a full solution.

Re: Reverse references

Tue, 15 Dec 2015 20:35:20 GMT

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.