Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Two tables, the load/select statements as follows:
LOAD event_id, data_id AS data_amne_id;
SQL SELECT * FROM `innan_portning`.`bokning_amnen`;
OUTER Join LOAD id AS data_amne_id, namn AS namn_amne;
SQL SELECT * FROM `innan_portning`.`data_amnen`;
The Key that should combine the two tables are the "data_amne_id"-field.
The two tables contain the following:
bokning_amnen:
event_id data_amne_id
200 1
201 3
202 10
203 2
........................
data_amnen:
data_amne_id namn_amne
1 Math
2 Phys
3 Biologi
4 Chemi
........................
What I would like is a table like this:
bokning_amnen:
event_id namn_amne
200 Math
201 Biologi
202 Tech
203 Phys
........................
But at the moment all the fields are overwritten with the first textstring, for example "Math".
Would love some help!
I have tested ur data ...
its working perfectly in Mine.
I'm afraid I cannot test your QW-file since im trying it out with an Personal edition.
My table becomes:
bokning_amnen:
data_amne_id event_id namn_amne
1 200 Math
1 201 Math
1 202 Math
1 203 Math
Yours doesn't look like this?
thanks.
Im afraid I was wrong.
The sort-order got changed and since my table contained alot more entries in a similar order I didn't realize this.
Thanks for confirming it, made me look closer! 🙂
cheers
Hello,
The associative logic will display proper results with no need of joining. So in your model above, should you load both tables and select value "201" in field event_id you will get "BIologi" in field namn_amne.
Anyway, if you require to get only one file, try the following:
IdNameMap:
MAPPING LOAD data_amne_id,
namn_name;
SQL SELECT * FROM `innan_portning`.`data_amnen`;
TableWithNames:
LOAD event_id,
ApplyMap('IdNameMap', data_id, 'No correspondence') AS namn_name;
SQL SELECT * FROM `innan_portning`.`bokning_amnen`;
Hope that helps.
BI Consultant
A follow-up:
If I would like to remove the previous Key (data_amne_id), since i joined the tables and therefor this field isn't really needed. Im trying to use the following but it doesn't remove the field:
INNER JOIN First 100 LOAD id AS data_amne_id, namn AS namn_amne;
DROP Field data_amne_id;
SELECT * FROM data_amnen;
Any ideas?
Hi,
What about the mapping table above? Does that suit you?
Regards
BI Consultant
Woops, wrote my reply meanwhile you posted yours so missed it
I haven't reached the mapping-sections in the manual yet so am abit unsure how it works. But if I copy-paste it to my script it shows me the error "Field not found <data_amne_id>" (?).
b.r.
Hi,
Take the following script as an example:
CodesNamesMap: // I'm using INLINE load, data can come from any other source
MAPPING LOAD * INLINE [
Code, Name
1, Maths
2, Philosophy
3, Bio
];
IdCodeTable:
LOAD Id, ApplyMap('CodesNamesMap', Code, 'No Name') AS Name INLINE [
Id, Code
200, 1
201, 1
202, 3
203, 8
];
According to the above, it should return the following output
200, Maths
201, Maths
202, Bio
203, No Name
Check the attached file just in case.
Hope that helps
BI Consultant