topic Re: Script Testing - Filtering Help in QlikView

Script Testing - Filtering Help

kwarren1 — Tue, 20 Sep 2016 19:11:35 GMT

Hello,

The following script statement is still scanning through 50 Million records and taking about 15 minutes to re-load the script. Is there anything I can do to more effectively filter or retrieve a sample or smaller size of the records I need in order to ensure testing?

tempDayPoint_Measurement_Summry1:

LOAD

Resident Point_Measurement

Where _kHour_Quarter_ID_Autonumber <= (100)

Re: Script Testing - Filtering Help

MarcoWedel — Tue, 20 Sep 2016 21:54:28 GMT

maybe using the sample prefix:

Sample ‒ QlikView

hope this helps

regards

Marco

Re: Script Testing - Filtering Help

adamdavi3s — Wed, 21 Sep 2016 07:04:13 GMT

You can also use the option under debug to load just x number of rows.

This will just load the first x, Marco's solution is probably better for testing as it will provide a random sample.

Re: Script Testing - Filtering Help

berkarmagan — Wed, 21 Sep 2016 07:09:45 GMT

Just use debug on reload page, u can get your sample faster.

Re: Script Testing - Filtering Help

Anonymous — Wed, 21 Sep 2016 08:01:43 GMT

Hi Kevin, use first statement to load the number of first rows that you want to load. If your statement is SQL, then use top functions to do it.

Regards!

Re: Script Testing - Filtering Help

kwarren1 — Wed, 21 Sep 2016 13:03:23 GMT

Unfortunately the source is not OLE DB or ODBC so SQL statements are not an option

Re: Script Testing - Filtering Help

kwarren1 — Wed, 21 Sep 2016 13:08:36 GMT

Marco. Thanks for the response, but the sample prefix actually makes the load go slower. I put in "sample .1 " before the load statement and it seemed to move at 1 tenth of the speed instead of running 10 times faster because only a tenth of the data was being pulled.

Re: Script Testing - Filtering Help

el_aprendiz111 — Wed, 21 Sep 2016 14:14:01 GMT

Hi,

tempDayPoint_Measurement_Summry1:

First 1000 // 20000, 30000 any amount

LOAD

Resident Point_Measurement

Re: Script Testing - Filtering Help

kwarren1 — Wed, 21 Sep 2016 18:13:31 GMT

thank you. Your post was helpful, but bloomed a more direct question.

I was hoping there would be an option to load specific chunks of data for testing. For example, If my data set was 12 months for year 2015, It would be ideal to pull 1 specific month worth of data to cross reference and check against the source document.

It is helpful to have first 1000 records, but it is not specific to something I can test against. I will try ordering my fact table by date asc so I can pin-point the exact row of when my first month ends.

This method is only possible for January or December checking.

Isn't there an equivalent to the SQL Where Statement in Qlik Scripting?

Re: Script Testing - Filtering Help

Anil_Babu_Samineni — Wed, 21 Sep 2016 18:42:09 GMT

Try with transpose

tempDayPoint_Measurement_Summry1:

LOAD

Resident Point_Measurement

Where _kHour_Quarter_ID_Autonumber <= (100)

STORE tempDayPoint_Measurement_Summry1 into Path\tempDayPoint_Measurement_Summry1.qvd (qvd);

Then Load that qvd into Qlikview

Here, qvd is negotiate / reduce the Time elapse

Re: Script Testing - Filtering Help

Anonymous — Thu, 22 Sep 2016 06:10:10 GMT

Hi Kevin, First statement are for load not for SQL, regards!

Re: Script Testing - Filtering Help

Anonymous — Thu, 22 Sep 2016 06:12:03 GMT

LOAD

A, B, C, D,...

WHERE Date >= '01/01/2015' and Date <= '31/12/2015'

With where you can filter your load for specific data.

Regards

Re: Script Testing - Filtering Help

kwarren1 — Thu, 22 Sep 2016 13:28:19 GMT

Thank you. This did help the script run 2 min. faster for testing. I am finding out that my sum statements within my script is the bigger culprit for slow speed outputs.

Re: Script Testing - Filtering Help

kwarren1 — Thu, 22 Sep 2016 15:08:02 GMT

Thank You!

My run time was 1:08 instead of 15+ minutes when I used the STORE function and saved the table as a QVD.

My initial load was still 15 minutes, but then that table is saved as a QVD that can be repetitively referenced in code when I make other adjustments in the future.

Below is my final code output. Hope it helps someone in the future!

/******* STEP 1 - Join In New Field ********/
tempAgg_Table:
Noconcatenate
Load *
Resident Point_Measurement
;

Left Join (tempAgg_Table)

Load
_kHour_Quarter_ID_Autonumber,
DayDate
Resident Calendar_Table
;

/******* STEP 2 - Aggregate Table ********/
Agg_Point_Measurement:
Load

_kFctFacility_Summry as _kFctFacility_Summry,
DayDate,
_kPoint_ID as _kPoint_ID,
sum(CONSUMPTION) as CONSUMPTION

Resident tempAgg_Table

Group by

_kFctFacility_Summry,
DayDate,
_kPoint_ID
;

/******* STEP 3 - Store Aggregate Table as a QVD ********/
STORE * from Agg_Point_Measurement into U:\Users\cwarreke\Scratch Docs\Agg_Point_Measurement.qvd(qvd);

/******* STEP 4 - Substitute This Bit Of Code Instead of running STEP 2 & 3, Comment Out Step 2 & 3 moving forward********/
Load *
From (qvd);