Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Script Resident Load Issue

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?

1 Solution

Accepted Solutions
flipside
Valued Contributor II

Re: Script Resident Load Issue

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

5 Replies
Employee
Employee

Re: Script Resident Load Issue

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

flipside
Valued Contributor II

Re: Script Resident Load Issue

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

farolito20
New Contributor II

Re: Script Resident Load Issue

first 3 is to load the 3 first rows of this load?

flipside
Valued Contributor II

Re: Script Resident Load Issue

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.

Not applicable

Re: Script Resident Load Issue

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.

Community Browser