I got confusion on joining two diff datasets. I have loaded SQL dataset & I have created some dashboards. Now, I want to join two fields(columns) from excel file. So, how do I have to do.
1. In the data load editor, do I have to write scripts in the same auto generated data field or should I open a new connection to write the join script?
2. What is the correct script to join them ?
3. Also I have a field name in my excel file, it is not in date format? How do I do it ?
please help me out.
1.yes you need to create other connection for loading excel data
2.just use the join key word between the two tables(which type of join you need left join right join or inner join or outer join)
3.can you share the date format of the field in the excel file and date format which you required in qlik.
Before joining 2 data set, i have few question.
1. Is there any link between SQL and excel?
2. How do you load SQL data? are you using data manager or load editor?
Thanks Hemeswara Reddy.. Step 1 is fine..
I need clarification in step 2. Am loading excel in separate connection. Then I have to open a new tab in data load editor then I have to write script to join SQL & excel right.
LIB CONNECT TO '22.214.171.124';
LOAD "Emp #",
SQL SELECT "Emp #",
(ooxml, embedded labels, table is [TestEmp]);
Is it correct.
Then, Step 3, I have date format as 08/22/2017. Also checked that the number format is assigned to Date. Still when I load in to Qliksense it is coming up as Value.
Thanks for the response. Yes, I have used join statement to combine SQL & Excel file. I am ok with bringing in two datasets,. I use data manager initially & then for joining excel file. I have used data load editor to get the fields form SQL & then joined it with excel. Now struck with the date format as it is showing as date in excel & while loading on to Qliksense. It is coming up as a value.
With reference to Re: Excel import, date format wrong? . I tried the solution given by Ruben & it works fine. I'm able to load the excel file as Date format.
Format: Date("Field Name", 'YYYY-MM-DD') as "Field Name"
Eg: Date("CREATED DATE", 'YYYY-MM-DD') as "CREATED DATE"
can u check qliksense date format
in main tab
and you can convert it by using
Date(Fieldname,'exceldateformat') as fieldname
or you can use
Date(Date#(fieldname,'Exceldateformat'),'format require in qlik') as fieldname