Use nested SQL for Excel Load

[Anonymous]

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.

[sasiparupudi1]

Is this related to qlikview?

[Anonymous]

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

[wdchristensen]

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

[Anonymous]

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]

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

[Anonymous]

Works perfectly, Thank you