Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm trying to load in a script as below but I'm getting a "Field not found" error. Any ideas what I'm doing wrong?
Let vToday = Today();
Let vAYearAgo = Date(AddYears(Today(),-1),'YYYY-MM-DD');
StaffRecord:
LOAD 'Yearly' As [Frequency],
if(not Status = '',1,0) as EntryExists,
if(Status = 'Completed',1,0) as Completed,
if(Status = 'Requested',1,0) as Booked,
if(Status = 'Did Not Attend',1,0) as DNA,
if(not Status = 'Requested' and not Status = 'Completed',1,0) as Incomplete,
[Staff Group],
[Date]
FROM
[SPREADSHEET.xlsx]
(ooxml, embedded labels, header is 1 lines)
where RowNo() < 1000;
DNAReport:
First 3
LOAD [Staff Group],
Sum([DNA]) as [TotalDNA]
Resident StaffRecord
where [Date] > $(#vAYearAgo) and [Date] < $(#vToday)
group by [Staff Group]
order by [TotalDNA] ASC;
Sorry that it's a very specific query, but I think it's my knowledge of the resident load that's causing me problems. That, or the order/group by.
Cheers!
EDIT: I'm now certain it's to do with the order by, as it works without it in. Is there any way to order by the fields, or will I have to do the query on the chart itself to only select the top 3?
You can do this by a 2-step approach, first GROUP BY the field, then inner join itself by the ORDER BY ...
DNAReport:
Load [Staff Group], Sum(DNA) as TotalDNA
Resident StaffRecord
group by [Staff Group];
inner join
first 3 load [Staff Group], TotalDNA
resident DNAReport
order by TotalDNA;
Another way is to use an ODBC connection to your excel file where you can group by and order by in one command ...
ODBC CONNECT32 TO [Excel Files;DBQ=StaffGroup.xls];
SQL SELECT top 3 [Staff Group], Sum(DNA) as TotalDNA
FROM `StaffGroup`.`Sheet1$`
group by [Staff Group]
order by Sum(DNA);
flipside
The filed TotalDNA does not exist in the table StaffRecord.
In QlikVIew 11 you can set a chart in the layout to show only the top 3.
HIC
You can do this by a 2-step approach, first GROUP BY the field, then inner join itself by the ORDER BY ...
DNAReport:
Load [Staff Group], Sum(DNA) as TotalDNA
Resident StaffRecord
group by [Staff Group];
inner join
first 3 load [Staff Group], TotalDNA
resident DNAReport
order by TotalDNA;
Another way is to use an ODBC connection to your excel file where you can group by and order by in one command ...
ODBC CONNECT32 TO [Excel Files;DBQ=StaffGroup.xls];
SQL SELECT top 3 [Staff Group], Sum(DNA) as TotalDNA
FROM `StaffGroup`.`Sheet1$`
group by [Staff Group]
order by Sum(DNA);
flipside
first 3 is to load the 3 first rows of this load?
Yes, the "first 3" will bring back the top 3 rows in ascending order of TotalDNA as per the original requirement. When this is inner-joined back to the same table it effectively drops the data not needed.
I'm having the same problem, but in the answer they forgot the filter:
where [Date] > $(#vAYearAgo) and [Date] < $(#vToday)
.
This is my script:
LOAD `IT_ICODE` as [Codigo de Producto],
`IT_WHCODE` as [Codigo Almacen],
`IT_LOCAL` as Sucursal,
`IT_QTY_SHP` as QtySales;
SQL SELECT *
FROM `C:\USERS\LAURA NU\DOCUMENTS\COMETA\QLIK VIEW\COMETA\DBF`\itransd;
QtyVendida:
load [Codigo de Producto],
sum(QtySales) as QtyVendida2
resident Ventas
where QtySales >0;
And is very important for me to filter with positive values.