How to implement the below query in Qlikview

Thu, 09 Apr 2015 23:49:59 GMT

SELECT DISTINCT

(SELECT count(number) FROM EPISODE C, CALENDAR D

where c.start_date = d.CALENDAR_DATE

and d.CALENDAR_YEAR = F.CALENDAR_YEAR

and d.MONTH= F.MONTH)as ADM, count(g. stay_number)

FROM CALENDAR F, EPISODE G

where F.CALENDAR_YEAR = '2014'

and F.MONTH = 'Apr'

and F.CALENDAR_DATE = G.episode_end_date

GROUP by F.CALENDAR_YEAR, F.MONTH_SHORT_DESC

Re: How to implement the below query in Qlikview

ramoncova06 — Fri, 10 Apr 2015 04:41:19 GMT

this is from QV help section, this can be done, but I guess it would be better if you understood the whole thing

Load

The load statement loads fields from a file, from data defined in the script, from a previously loaded table, from a web page, from the result of a subsequent select statement or by generating data automatically. The general syntax of the load statement is:

load [ distinct ] *fieldlist

[( from file [ format-spec ] |

from_field fieldassource [format-spec]

inline data [ format-spec ] |

resident table-label |

autogenerate size )]

[ where criterion | while criterion ]

[ group_by groupbyfieldlist ]

[order_by orderbyfieldlist ]

where:

distinct is a predicate used if only the first of duplicate records should be loaded.

*fieldlist ::= ( * | field { , field } )

A list of the fields to be loaded. Using * as field list indicates all fields in the table.

field ::= ( fieldref | expression ) [ as aliasname ]

The field definition must always contain a literal, a reference to an existing field, or an expression.

fieldref ::= ( fieldname |@fieldnumber |@startpos:endpos [ I | U | R| B ] )

fieldname is a text that is identical to a field name in the table. Note that the field name must be enclosed by straight double quotation marks or square brackets if it contains e.g. spaces. Sometimes field names are not explicitly available. Then a different notation is used:

@fieldnumber represents the field number in a delimited table file. It must be a positive integer preceded by "@". The numbering is always made from 1 and up to the number of fields.

@startpos:endpos represents the start and end positions of a field in a file with fixed length records. The positions must both be positive integers. The two numbers must be preceded by "@" and separated by a colon. The numbering is always made from 1 and up to the number of positions. If @startpos:endpos is immediately followed by the characters I or U, the bytes read will be interpreted as a binary signed (I) or unsigned (U) integer (Intel byte order). The number of positions read must be 1, 2 or 4. If @startpos:endpos is immediately followed by the character R, the bytes read will be interpreted as a binary real number (IEEE 32-bit or 64 bit floating point). The number of positions read must be 4 or 8. If @startpos:endpos is immediately followed by the character B, the bytes read will be interpreted as a BCD (Binary Coded Decimal) numbers according to the COMP-3 standard. Any number of bytes may be specified.

expression can be a numeric function or a string function based on one or several other fields in the same table. For further information, see the syntax of Script Expressions.

as is used for assigning a new name to the field.

Note!
If a table contains two fields with the same field name, typically when loading from a text file,only one field can be loaded even though the field names have been changed using as.

from is used if data should be loaded from a file.

file ::= [ path ] filename

The path is the path to the file, either absolute, or relative to the QlikView document (.qvw file). If the path is omitted, QlikView searches for the file in the directory specified by the directory statement. If there is no directory statement, QlikView searches in the working directory, which is usually the directory in which the QlikView file is located. The path may also be a URL address (HTTP or FTP), pointing to a location on the Internet or an intranet.

The filename may contain the standard DOS wildcard characters ( * and ? ). This will cause all the matching files in the specified directory to be loaded.

format-spec ::= ( fspec-item { , fspec-item } )

The format specification consists of a list of several Format Specification Items, within brackets.

from_field is used if data should be loaded from a previously loaded field.

fieldassource ::= (tablename, fieldname)

The field is the name of the previously loaded tablename and fieldname.

format-spec ::= ( fspec-item {, fspec-item } )

The format specification consists of a list of several Format Specification Items, within brackets.

inline is used if data should be typed within the script, and not loaded from a file. Use the Inline Data Wizard for help with the creation of load inline statements.

data ::= [ text ]

Data entered through an inline clause must be enclosed by double Quotation Marks in Scripting or with square brackets. The text between these is interpreted in the same way as the content of a file. Hence, where you would insert a new line in a text file, you should also do it in the text of an inline clause, i.e. by pressing the Enter key when typing the script.

resident is used if data should be loaded from a previously loaded table.

table label is a label preceding the load or select statement(s) that created the original table. The label should be given with a colon at the end.

autogenerate is used if data should be automatically generated by QlikView.

size ::= number

Number is an integer indicating the number of records to be generated. The field list must not contain expressions which require data from a database. Only constants and parameter-free functions (e.g. rand(), recno()) are allowed in the expressions.

where is a clause used for stating whether a record should be included in the selection or not. The selection is included if criterion is true.

while is a clause used for stating whether a record should be repeatedly read. The same record is read as long as criterion is true. In order to be useful, a while clause must typically include the IterNo( ) function.

criterion is a logical expression.

group by is a clause used for defining over which fields the data should be aggregated (grouped). The aggregation fields should be included in some way in the expressions loaded. No other fields than the aggregation fields may be used outside aggregation functions in the loaded expressions.

groupbyfieldlist ::= (fieldname { ,fieldname } )

order by is a clause used for sorting the records of a resident table before they are processed by the load statement. The resident table can be sorted by one or more fields in ascending or descending order. The sorting is made primarily by numeric value and secondarily by national ASCII value. This clause may only be used when the data source is a resident table. The ordering fields specify which fields the resident table is sorted by. The field can be specified by its name or by its number in the resident table (the first field is number 1).

orderbyfieldlist ::= fieldname [ sortorder ] { , fieldname [ sortorder ] }

sortorder is either asc for ascending or desc for descending. If no sortorder is specified, asc is assumed.

fieldname, path, filename and aliasname are text strings representing what the respective names imply. Any field in the source table can be used as fieldname. However, fields created through the as clause (aliasname) are out of scope and cannot be used inside the same load statement.

Note!
If no source of data is given by means of a from, inline, resident, from field or autogenerate clause, data will be loaded from the result of the immediately succeeding select or load statement. The succeeding statement should not have a prefix.

Examples:

Different file formats

Load * from data1.csv;

Load * from 'c:\userfiles\data1.csv' (ansi, txt, delimiter is ',', embedded labels);

Load * from 'c:\userfiles\data2.txt' (ansi, txt, delimiter is '\t', embedded labels);

Load * from file2.dif (ansi, dif, embedded labels);

Load @1:2 as ID, @3:25 as Name, @57:80 as City from data4.fix (ansi, fix, no labels, header is 0, record is 80);

Load * from C:\qdssamples\xyz.qvx (qvx);

Selecting certain fields, calculating fields

Load FirstName, LastName, Number from data1.csv;

Load @1as A, @2 as B from data3.txt' (ansi, txt, delimiter is '\t', no labels);

Load FirstName&' '&LastName as Name from data1.csv;

Load Quantity, Price, Quantity*Price as Value from data1.csv;

Selecting certain records

Load distinct FirstName, LastName, Number from data1.csv;

Load * from Consumption.csv where Litres>0;

Loading data not on file

Load * Inline

[CatID, Category

0,Regular

1,Occasional

2,Permanent];

Load * Inline [UserID, Password, Access

A, ABC456, User

B, VIP789, Admin];

Load RecNo( ) as A, rand( ) as B autogenerate(10000);(Note: The parenthesis after autogenerate is allowed but not required.)

Loading data from previously loaded table

tab1:

Select A,B,C,D from transtable;

Load A,B,month(C),A*B+D as E resident tab1;

Load A,A+B+C resident tab1 where A>B;

Load A,B*C as E resident tab1 order by A;

Load A,B*C as E resident tab1 order by 1,2;

Load A,B*C as E resident tab1 order by C desc, B asc, 1 desc;

Loading data from previously loaded fields

Load A from_field (Characters, Types);

Loading data from succeeding table

Load A, B, if(C>0,'positive','negative') as X, weekday(D) as Y;

Select A,B,C,D from Table1;

Grouping data

Load ArtNo, round(Sum(TransAmount),0.05) as ArtNoTotal from table.csv group by ArtNo;

Load Week, ArtNo, round(Avg(TransAmount),0.05) as WeekArtNoAverages from table.csv group by Week, ArtNo;

Reading one record repeatedly

My Tab:

Load Student,

mid(Grades,IterNo( ),1) as Grade,

pick(IterNo( ), 'Math', 'English', 'Science', 'History') as Subject from Tab1.csv

while mid(Grades,IterNo( ),1)<>' ';

Re: How to implement the below query in Qlikview

Fri, 10 Apr 2015 06:36:40 GMT

My Actual issues is I want to count IDs based on start and end date from the same table

For example

ID, start, end, country

1, 1/1/14, 2/1/14, AUS

2, 2/1/14, 2/1/14, US

3, 2/1/14, 3/1/14, AUS

4, 3/1/14, 3/1/14, US

5, 2/1/14, 4/1/14, AUS

If I select date 2/1/14

I want to see

Date Admissions Separations

2/1/14 Count(ID)= 3 Count(ID) = 2

Country & Date Admissions Separtions

AUS & 2/1/14 Count(ID)= 2 Count(ID) =1

or some other filters like country , city and all

based on the series of selection I want to change the admissions and Separations count.

Regards

Karthik Daibala

Re: How to implement the below query in Qlikview

shivanandk — Tue, 14 Jul 2015 22:52:46 GMT

What is your data source ? If it in Database, you can execute the statement in QlikView Script directly to load data from the database using the SQL keyword before the statement.

topic Re: How to implement the below query in Qlikview in QlikView

How to implement the below query in Qlikview

Re: How to implement the below query in Qlikview

Load

Re: How to implement the below query in Qlikview

Re: How to implement the below query in Qlikview