topic Re: Use nested SQL for Excel Load in QlikView

Use nested SQL for Excel Load

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have created a nested SQL to load data from a datatbase. This works fine if my data are in a Database, but for loading the data from an Excel file this don't work. I suppose it is not supported by the ODBC Driver for the Excel file.

The SQL is:

select
a.T1 AS Data1,
a.T2 As Data2,
a.T3 AS Data3,
Count(a.T4) AS Data4,
(Select count(e.T4) FROM Table1 e WHERE e.T3 < a.T3 and e.T1 = a.T1 and e.T2 = a.T2) AS Data5
From Table1 a
Group by a.T1,a.T2,a.T3;

Maybe it is quiet simple, but I'm not able to find a solution I tried to load the tables and then work with resident tables. But this does not work. I also tried to find a solution with an Aggregation function in the diagram Formular, bu also there I do not find a solution.

Re: Use nested SQL for Excel Load

sasiparupudi1 — Wed, 28 Mar 2018 14:39:12 GMT

Is this related to qlikview?

Re: Use nested SQL for Excel Load

Anonymous — Wed, 28 Mar 2018 14:41:32 GMT

Yes it is about the Skript to load the data to Qlikview

Re: Use nested SQL for Excel Load

wdchristensen — Wed, 28 Mar 2018 16:51:24 GMT

Can you load the raw data from the Excel file into Qlik? If so, you could write aggregation in Qlik syntax.

Re: Use nested SQL for Excel Load

Anonymous — Thu, 29 Mar 2018 06:45:19 GMT

Yes I can load it from Excel into Qlik. It was my firt idea to write the Aggregation in QlikView Syntax. But first I was not successfull and second I thought it would be faster to do it in the Load Script as the File has several 1000 data rows and to calcualte it on time when displaying the graph seems quiet time consuming.

Do you have an example how to write the Aggregation function in the Qlik Syntax?

Re: Use nested SQL for Excel Load

sasiparupudi1 — Thu, 29 Mar 2018 07:42:08 GMT

It is hard to give a solution without looking at your data in excel but may be you can do something like below

Table1:

select

T1 AS Data1,

T2 As Data2,

T3 AS Data3,

From Table1 ;

T1:

NoConcatenate Load

Data1,

Data2,

Data3,

T4,

If(Data1=Previous(Data1) AND Data2=Previous(Data2) AND Data3<Previous(Data3),1,0) as Flag

Resident Table

Order By Data1,Data2,Date3,T4

;

Drop Table Table1;

Left Join(T1)

LOAD

Data1,

Data2,

Data3,

Count(T4) AS Data4

Resident T1

Group By

Data1,

Data2,

Data3

;

Left Join(T1)

LOAD

Data1,

Data2,

Data3,

Count(T4) AS Data5

Resident T1

Where Flag=1

Group By

Data1,

Data2,

Data3

Re: Use nested SQL for Excel Load

Anonymous — Thu, 29 Mar 2018 08:59:32 GMT

Works perfectly, Thank you