Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I am very new to qlik and am not good with script. I want to be able to "Join" 3 different columns. I was told that I probably need an "inner" join but I am not sure.
My Email, Customer Type and Phone columns from both tables have data in both, different data or data in one or the other.
Example:
The emails above are fake, obviously I want to join the columns, not what is just shown in the example.
Below is my script stuff, I have no idea how to write this join script. I tried using the documentation but failed numerous times. If someone could help me write the script for the email column, I can then apply it to the other columns I need. Thanks!
[Sheet1_b8b91eb5-2f56-7a87-2d07-3f055682]:
LOAD [Company],
Company as ACompany,
[Territory],
[Marketing Segment],
[Customer Type] as ACustomerType,
[Primary Contact (Account)],
[Email] as AEmail,
[Phone] as APhone,
[Account Manager] as AAccountManager,
[Topic/Project],
[Gross Profit],
[Status Type],
[PO Value],
[Actual Close Date],
[Actual Revenue],
[Currency]
FROM [lib://qlikid_shaebert/Account_test_Updated.xlsx]
(ooxml, embedded labels, table is Sheet1);
[Sheet1_9788250d-be08-350b-01fc-01f33868]:
LOAD [First Name],
[Last Name],
[Email],
[Event Type],
[Account Manager],
[Job Title],
[Company]as ACompany,
[Phone],
[Event Style],
[Customer Type]
FROM [lib://qlikid_shaebert/Raw_Inport_Test_Updated.xlsx]
(ooxml, embedded labels, table is Sheet1);
RENAME TABLE [Sheet1_b8b91eb5-2f56-7a87-2d07-3f055682] TO [Sheet1];
RENAME TABLE [Sheet1_9788250d-be08-350b-01fc-01f33868] TO [Sheet1-1];
This is an example; I made the assumption there is a common field between the 2 tables (User); I used the common field to join the 2 tables, so I can choose one of the 2 email values (bold)
// first table
T1:
load * inline [
User, Email
u1, u1@qlik.com
u2, u2@qlik.com
u3, u3@qlik.com
u4,
];
join (T1)
// second table
load User, Email as Email2 inline [
User, Email
u2, u2@qlik.com
u3, u333@qlik.com
u4, u4@qlik.com
u5, u5@qlik.com
];
Left Join (T1)
LOAD
User,
// peek the Email if not empty, else Email2
if(len(trim(Email))>0, Email, Email2) as NewEmail
Resident T1;
Thank you for the help, but I have a couple questions...
if(len(trim(Email))>0, Email, Email2) as NewEmail
^ Do I have to replace Email and Email2 with the data I included in my post? (Email, AEmail)
u1, u1@qlik.com
u2, u2@qlik.com
u3, u3@qlik.com
u4,
];
^ By using this code I will be able to apply this Join to the whole column, all rows? I need a code that does not include the example emails, the email addresses are unknown to me and are being pull from one table or the other.
Again, the @qlik.com examples were only examples, I have hundreds of rows of information that needs to work for and the emails have different domains (@qlik.com,@gmail.com,@yahoo.com, etc). Will I have to do something like...
u1, ""
u2, ""
u3, ""
u4, ""
u5, ""
......
u150, ""
u151 ""
];
Id prefer not to have to do that because my maximum number of rows will change over time.
specifically I need to Join two columns from 2 different tables.