Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
bernharddahm
Contributor
Contributor

Use nested SQL for Excel Load

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.

6 Replies
sasiparupudi1
Master III
Master III

Is this related to qlikview?

bernharddahm
Contributor
Contributor
Author

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

wdchristensen
Specialist
Specialist

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

bernharddahm
Contributor
Contributor
Author

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?

sasiparupudi1
Master III
Master III

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,

T4

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

bernharddahm
Contributor
Contributor
Author

Works perfectly, Thank you