topic Script not fetching the rows in QlikView

Script not fetching the rows

Anonymous — Tue, 29 Jun 2010 04:11:25 GMT

I was trying to create a report from the table which has more than 3 million rows, after starting reloading the script the process stop in between saying the virtual memory is full , so i stopped the process and re-wriiten the script for limited time , just tried to run the data for couple of months instead of taking all the data from table, but i am not able to run the script, it just hangs...

it says executing scripts but no progress is seen, i deleted the application and created all over again , but still no progress...

could any one please explain what happened and what should i do now for running the script?

Please Help...

Script not fetching the rows

sparur — Tue, 29 Jun 2010 06:28:28 GMT

try to use statement FIRST n before LOAD statement.

For example:

First 1000

LOAD * FROM...

If your script will not reload successfully, so you have very bad data model (may be you have synthetic keys, cyclic references or something else)

Can you post your example QVW ?

Script not fetching the rows

Anonymous — Tue, 29 Jun 2010 18:01:58 GMT

here is the script i am having..

set

ThousandSep=','

;

SET

DecimalSep='.'

;

SET

MoneyThousandSep=','

;

SET

MoneyDecimalSep='.'

;

SET

MoneyFormat='$#,##0.00;($#,##0.00)'

;

SET

TimeFormat='h:mm:ss TT'

;

SET

DateFormat='M/D/YYYY'

;

SET

TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT'

;

SET

MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'

;

SET

DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'

;

CONNECT

TO [Provider=MSDAORA.1;User ID=piowner;Data Source=pidwprd] (XPassword is

BLRcASVOFbNIWaVNQbXB);

SQL

SELECT

* FROM

PIOWNER."DW_T_JOBDATA" where start_time > =

'15-jun-2010';

Script not fetching the rows

Oleg_Troyansky — Tue, 29 Jun 2010 18:20:25 GMT

My guess is that your SQL Server is having difficulties performing a SELECT on a large table with no index on a date. Try creating an index and running the same query. Also, check that your syntax with regards to the date representation is correct.

Script not fetching the rows

Anonymous — Tue, 29 Jun 2010 18:36:09 GMT

the table has indexes,

how do i select last 1000 rows instead of first 1000 rows?

when i right select statement something like

select * from dw_t_jobdata where start_time > '01-jun-2010' , i get an error saying start_time not found, if i just write a select statement without where condition it works..i basically want recent data ...how do i get that...

Script not fetching the rows

Tue, 29 Jun 2010 22:14:33 GMT

I think your initial issue is memory related, ideally you should have 1gb of RAM available (physical memory) for every 1-2 million rows you plan to load. This needs to be on the machine that is doing the loading.

If you are getting the error "start_time" not found that suggests there is no field called start_time or the format of the date you supplied in the where clause did not match the format of the date in the table.

if start_time is definately a valid field, it may be easier to rewrite it as


select *
from dw_t_jobdata where start_time = max(start_time);

and if that works, you can expand the dataset by changing it to


select *
from dw_t_jobdata where start_time = max(start_time)+30;

which would give you the last month.

Script not fetching the rows

Anonymous — Tue, 29 Jun 2010 22:30:08 GMT

start_time field is certainly there in the table

Here is my current query which works fine but when i give any where condition it throws error, mine is oracle db.

FIRST

50000

SQL

SELECT

TO_CHAR(START_TIME,'MM/DD/YYYY')

START_DAY,

TO_CHAR(FINISH_TIME,'MM/DD/YYYY')

FINISH_DAY,

"JOB_ID",

"USER_NAME",

"QUEUE_TIME",

"START_TIME",

"FINISH_TIME",

"PROJECT_NAME",

"QUEUE_NAME",

"CLUSTER_NAME",

"LSFHOST_MODEL",

"HOST_NAME",

"LSFHOST_TYPE",

"MEM_REQUEST",

"MEM_USAGE",

"SWAP_USAGE",

"RUN_TIME",

"CPU_MINUTES",

"PENDING_TIME",

"JOB_EXIT_STATUS",

"JOB_EXIT_CODE",

"JOB_NAME",

"JOB_ARRAY_INDEX",

"JOBRES_RAW",

"JOB_CMD"

FROM

PIOWNER."DW_T_JOBDATA"

;

Script not fetching the rows

Tue, 29 Jun 2010 22:38:28 GMT

I know QV is case sensitive in the the majority of the load script but can't rememebr off hand if that includes the SQL select statment. I say this as field name is being designated in the select as "START_TIME" (all uppercase), therefore stands to reason it should be uppercase in the where clause.

Script not fetching the rows

Anonymous — Tue, 29 Jun 2010 22:50:15 GMT

I tried below query , but i get an error saying invalid number , if i use max i get an error saying cannot use group funtion , if i use the date in single quotes like '06/01/2010' the scripts gets hanged ....Please Help

SQL

SELECT

TO_CHAR(START_TIME,'MM/DD/YYYY')

START_DAY,

TO_CHAR(FINISH_TIME,'MM/DD/YYYY')

FINISH_DAY,

"JOB_ID",

"USER_NAME",

"QUEUE_TIME",

"START_TIME",

"FINISH_TIME",

"PROJECT_NAME",

"QUEUE_NAME",

"CLUSTER_NAME",

"LSFHOST_MODEL",

"HOST_NAME",

"LSFHOST_TYPE",

"MEM_REQUEST",

"MEM_USAGE",

"SWAP_USAGE",

"RUN_TIME",

"CPU_MINUTES",

"PENDING_TIME",

"JOB_EXIT_STATUS",

"JOB_EXIT_CODE",

"JOB_NAME",

"JOB_ARRAY_INDEX",

"JOBRES_RAW",

"JOB_CMD"

FROM

PIOWNER."DW_T_JOBDATA" WHERE TO_CHAR(START_TIME,'MM/DD/YYYY') > 06/01/2010

;

Script not fetching the rows

Wed, 30 Jun 2010 00:14:25 GMT

The function "to_char" is converting the date to a string, i am not sure you want to be loading it in already pre-converted as QV should be handling as much of the data manipulation as possible outwith the select statment. String functions also tend to be more of a strain on SQL servers than straight numerical functions.

however, if you keep the to_char, you should be able to enter a wildcard match such as

PIOWNER."DW_T_JOBDATA" WHERE TO_CHAR(START_TIME,'MM/DD/YYYY') like '06/%'

to return everything for the 6th month although doesn't give you any control over the year.

i forgot to say you need to use a subquery for the where clause if you are usuing max, try

SQL SELECT START_TIME, USER_NAME from PIOWNER."DW_T_JOBDATA" where START_TIME = (select max(START_TIME) from PIOWNER."DW_T_JOBDATA")

and if that works, add fields one by one to the main query

If you have a PL/SQL editor such as TOAD it would be worth fine tuning the select statement with that first, then you can rule any syntax issues out.