Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Is this related to qlikview?
Yes it is about the Skript to load the data to Qlikview
Can you load the raw data from the Excel file into Qlik? If so, you could write aggregation in Qlik syntax.
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?
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
Works perfectly, Thank you