Cross Reference Table

Anonymous — Fri, 20 Apr 2018 16:41:32 GMT

So, Lets start out with scope.

I work in a plant that gathers thousands of lines of data minute.

my current QVF file has ~9 million lines of data and takes over 20 minutes to load (when I reload the data)

My coworkers spend hours manually filtering excel spreadsheets and working with an obsolete database.

I'm trying to combine a few assembly lines of data so I can cross reference part numbers in an assembly.

Currently I have an engine number "TPCEKxxxxx" (general serial number layout)

Head = "S"

Crank = "U"

Block = "P"

Block Sample Data

Line	Station	Station Timestamp	Part Number	Model	Pass Fail	Part Status	Part Type
2700	20.1	1/22/2018 4:27	0218P06260	336	Pass	Online	Other
2700	20.1	1/21/2018 15:46	0218P06070	336	Pass	Online	Other
2700	20.1	1/21/2018 15:36	0218P06250	336	Pass	Online	Other

Assembly Line Data Sample

Line	Station	Station Timestamp	Part Number	Model	Pass Fail	Part Status	Part Type	Pallet Number	Equipment
2880	20	1/22/2018 1:47	TPCEK022810031	P68229651AE	Pass	Online	Other	108	Depth 1
2880	20	1/22/2018 1:47	TPCEK022810031	P68229651AE	Pass	Online	Other	108	Depth 2
2880	20	1/22/2018 1:47	TPCEK022810031	P68229651AE	Pass	Online	Other	108	Force 1
2880	20	1/22/2018 1:47	TPCEK022810031	P68229651AE	Pass	Online	Other	108	Force 2
2880	20	1/22/2018 1:47	0148P04220	232	Pass	Part Married	Other	108	None

I want to Be able to choose a Block Number or an engine number and see all the other numbers associated with this. Timestamp is difficult due to it being different everywhere.

Currently I am using this code with a "key". I have a similar code for each didfferent sub component.

[Block Number]:

Load

[Station]&'-' & [Station Timestamp] as _key,

[Part Number] as [Block Number]

Resident [Sheet1]

where [Part Status] = 'Part Married' and match([Model],'336' ,'332', '232');

However, the cross referencing doesnt work both ways and doesnt seem to work very good.

Basically my problem is I have multiple part numbers under the "Part Number" column and I would like to match them up and cross reference them. Other columns like "Part Status" have a unique "Part Married"

Utlimately I would like a separate table that references all my other tables like:

Enigne Number Block number Crank Number Head number

TPCEK P U S etc.

topic Cross Reference Table in QlikView

Cross Reference Table