Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have to join two table with one columns how can I do this?
Table1--------country(filed)
Table2--------city(filed)
you're missing just the rename (it is the only way Qlik recognize a joining field)
try like this:
Table1:
LOAD
.
.
country as KEY
FROM...
LEFT JOIN(Table1)
LOAD
.
.
city as KEY
FROM...
Normally we can write before LOAD expression of table2 as:-
Left join(Table1)
it will work but I have to join Table1 with country field and table2 with city filed.
what is your expected output?
explain with sample data.
You can try with Concatenate instead of join
Regards,
Hi Prashanth,
I have two tables Injury and Participation which has around 30 columns in each table. Now, i want to join Injury with Participation on only one Column (Join on ID). How do i do this? I tried using Concatenate option but since i have the same column names in both the tables for some columns it is joining on those as well but i need the join to happen only on one Column i.e ID.
Can you please help me out
Thanks,
Vivek
you're missing just the rename (it is the only way Qlik recognize a joining field)
try like this:
Table1:
LOAD
.
.
country as KEY
FROM...
LEFT JOIN(Table1)
LOAD
.
.
city as KEY
FROM...
If you want to Join the two tables then concatenate is not the option you should use. Concatenate is like Union in sql. Sounds like you should do a left join:
Participation:
Load
Field1 AS %Join_ID,
Field2,
Field3
FROM XXXX;
left join(Participation)
FieldX AS %Join_ID,
FieldF
FROM XXXX;
QlikView will perform the join on the common fields. You need to make sure you name the ID field the same in the both tables.
Check this out for more on joins and concatenations:
If you have same column names in both your tables, Qlik will automatically associate ur 2 tables using these fields in common.
In fact, for Qlik, the key that associates 2 tables is the FIELD that shares the same NAME btwn 2 tables.
So, what you should do is RENAME these fields so that they won't be the same anymore and NAME your ID field in both tables the same way.
sthing like this :
Table1:
load field1,field2,field3,
YourID1 as KEY
from source1;
JOIN (Table1)
load fieldA,field2 as fieldB,fieldC,
YourID2 as KEY
from source2;
sorry but I am still confused with your explanation. Can you explain with sample data, so that we can provide exact solution .
Regards,
Hi Prashant,
Have you checked this link:
Understanding Join, Keep and Concatenate? It offers a detailed explanation.
Have a look here as well:
https://www.qlikviewaddict.com/2012/03/explaining-joins.html?m=1
https://help.qlik.com/en-US/sense/April2018/Subsystems/Hub/Content/Scripting/ScriptPrefixes/Join.htm
A sample app won't really provide any more information than that I am afraid.
Hope it helps.
Hi
I have multiple dimension table where each dimension tables has 4 keys and the fact table also has the same and it is creating as many synthetic keys for each.
For ex:
i have 3 tables with same columns so i want to create a data model as star schema how can i do this? i dont want single table data model ..... i want star schema model.
TableA ( DIMENSION1)
Id1
id2
id3
id5
E
F
TableB ( DIMENSION2)
id1
id2
id3
id4
G
H
FACT(F1)
id1
id2
id3
id4
Revenue
sales
FACT(F2)
id1
id2
id3
id4
Loss_revenue
Loss_sales
Etc.
How we can do the datamodeleing eliminating synthetic keys for the same. give some examples where if any one handled multiples keys between multiple dimension and multiple facts and removing synthetic keys.
Thanks