Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Firstly, I must apologise, I'm probably not explaining this that well.
Basically I'm trying to create an interface where bell ringers can find out what certain methods they have done, which bells they have rung etc by searching their name. Now, I've got two main tables at the moment; the raw data from the bell ringing website and a list of names to act as a primary key. To try to act as linking tables, I've also created tabes which link the "RingID" (like a session number, if you will) to "Name", and each table is named "bell1-2015", "bell2-2015" etc.
Unfortunately Qlik has done something weird and wonderful and created a dozen synthetic keys. Can someone possibly take a look as I'm sure I'm overlooking something simple; it's just been far too long since I've used this app!
Quick look at the main data table:
And here's the relationships it created...:
Here's the load script:
LOAD RingID,
Date,
Guild,
Town,
[Church/Address],
Borough,
Duration,
NoChanges,
Method,
[Additional Method Notes],
Pos1Name,
Pos1C,
Pos2Name,
Pos2C,
Pos3Name,
Pos3C,
Pos4Name,
Pos4C,
Pos5Name,
Pos5C,
Pos6Name,
Pos6C,
Pos7Name,
Pos7C,
Pos8Name,
Pos8C,
Pos9Name,
Pos9C,
Pos10Name,
Pos10C,
Pos11Name,
Pos11C,
Pos12Name,
Pos12C
FROM
(txt, utf8, embedded labels, delimiter is ',', msq);
LOAD RingID,
Name as Pos1Name
FROM
(txt, utf8, embedded labels, delimiter is ',', msq);
LOAD RingID,
Name as Pos2Name
FROM
(txt, utf8, embedded labels, delimiter is ',', msq);
LOAD RingID,
Name as Pos3Name
FROM
(txt, utf8, embedded labels, delimiter is ',', msq);
LOAD RingID,
Name as Pos4Name
FROM
(txt, utf8, embedded labels, delimiter is ',', msq);
LOAD RingID,
Name as Pos5Name
FROM
(txt, utf8, embedded labels, delimiter is ',', msq);
LOAD RingID,
Name as Pos6Name
FROM
(txt, utf8, embedded labels, delimiter is ',', msq);
LOAD RingID,
Name as Pos7Name
FROM
(txt, utf8, embedded labels, delimiter is ',', msq);
LOAD RingID,
Name as Pos8Name
FROM
(txt, utf8, embedded labels, delimiter is ',', msq);
LOAD RingID,
Name as Pos9Name
FROM
(txt, utf8, embedded labels, delimiter is ',', msq);
LOAD RingID,
Name as Pos10Name
FROM
(txt, utf8, embedded labels, delimiter is ',', msq);
LOAD RingID,
Name as Pos11Name
FROM
(txt, utf8, embedded labels, delimiter is ',', msq);
LOAD RingID,
Name as Pos12Name
FROM
(txt, utf8, embedded labels, delimiter is ',', msq);
LOAD
Name
FROM
(txt, utf8, embedded labels, delimiter is ',', msq);
So essentially what I need, is when someone searches their name, Qlik looks across Pos1Name, Pos2Name etc all the way to Pos12Name, but has them as a singular selectable item, changing the table to show any and all instances that they've rung a bell.
Really hope that makes sense!
Thanks again, as always,
Peter
I haven't really understood why you are loading the 12 tables.
Maybe, for now, load only the first table and then create an additional link table for your search using a CROSSTABLE LOAD:
CROSSTABLE (PosNameNr, Name)
LOAD RingID,
Pos1Name,
Pos2Name,
Pos3Name,
Pos4Name,
Pos5Name,
Pos6Name,
Pos7Name,
Pos8Name,
Pos9Name,
Pos10Name,
Pos11Name,
Pos12Name
FROM
(txt, utf8, embedded labels, delimiter is ',', msq);