Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
santho_ak
Partner - Creator III
Partner - Creator III

Combining SQL & Excel file, using Concat or Join function in Qliksense ?

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.

Thanks.

7 Replies
hemeshreddy
Partner - Contributor III
Partner - Contributor III

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.

marcohadiyanto
Partner - Specialist
Partner - Specialist

Hi Santhosh,

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

Marco

santho_ak
Partner - Creator III
Partner - Creator III
Author

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.

Eg:

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

LIB CONNECT TO '66.147.236.155';

LOAD "Emp #",

    "Term Date";

SQL SELECT "Emp #",

    "Term Date"

FROM "Test.dbo."Test1_Emp";

Join

LOAD

    "Emp #",

   "Term Date"

FROM [lib://TestEmp.xlsx]

(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.

santho_ak
Partner - Creator III
Partner - Creator III
Author

Hi Marco,

               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.

santho_ak
Partner - Creator III
Partner - Creator III
Author

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"

marcohadiyanto
Partner - Specialist
Partner - Specialist

Hi Santhosh,

I think you should concatenate rather than join, it will union the data.

Regards,

Marco

hemeshreddy
Partner - Contributor III
Partner - Contributor III

can u check qliksense date format

in main tab

set dateformat='';

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