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

[santho_ak]

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.

[hemeshreddy]

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]

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]

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]

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]

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]

Hi Santhosh,

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

Regards,

Marco

[hemeshreddy]

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