topic Re: First 1 load loading multiple rows in QlikView

First 1 load loading multiple rows

Fri, 17 Jul 2015 14:44:51 GMT

Hi,

we encountered this weird behavior in QV where we wanted to load a specific field of the first row from 12 QVDs. It ended up loading multiple rows (progressively) from succeeding files.

here is a simplified test script we ran:

Calendar2:

load '' as [KeyFiscalYearWeek2]

AutoGenerate 0;

Concatenate(Calendar2)

first 1 load

[Fiscal Year Week] as KeyFiscalYearWeek2

from $(vSourceTable_VendorSales)

(qvd);

where the from is a path with *.qvd pointing to 12 files.

this is the reload:

this is the table:

one work around we did (as the field is unique per file) is:

Concatenate(Calendar2)

first 1 load distinct

[Fiscal Year Week] as KeyFiscalYearWeek2

from $(vSourceTable_VendorSales)

(qvd);

where it apparently read only 2 rows per file:

It just so happened that the field we are loading is unique per file so the work around is ok. if for a different test case this may not apply.

Weve tried other work around(s) but i think a simple first 1 load should work the way it is intended. Some of our work arounds included adding fields to the autogenerate script, adding expressions in the first 1 load script (just mind boggling!)

Has anyone experienced this? is there anything we are missing?

thanks,

edwin.

Re: First 1 load loading multiple rows

Fri, 17 Jul 2015 19:35:38 GMT

Not sure why it is behaving like that. This is happening when the tables are being concatenated. Otherwise it is working as expected. Somebody from Qlik should explain this.

Instead of using FIRST 1, use WHERE condition like RecNo()=1 as below.

FYW:

LOAD

[Fiscal Year Week] as KeyFiscalYearWeek2

from $(vSourceTable_VendorSales)

(qvd) Where RecNo()=1;

Regards,

KKR

Re: First 1 load loading multiple rows

ramoncova06 — Fri, 17 Jul 2015 20:03:07 GMT

it seems as the file is duplicating itself, what for were you using before adding the variable or what is inside the variable ?

also try using a distinct

Re: First 1 load loading multiple rows

swuehl — Fri, 17 Jul 2015 20:10:31 GMT

What happens when you do an unoptimized LOAD? Performance shouldn't matter much because you want the first row anyway.

Calendar2:

load '' as [KeyFiscalYearWeek2]

AutoGenerate 0;

Concatenate(Calendar2)

first 1 load

[Fiscal Year Week] as KeyFiscalYearWeek2

from $(vSourceTable_VendorSales)

(qvd)

Where 1 = 1;

Re: First 1 load loading multiple rows

Mon, 20 Jul 2015 12:22:18 GMT

we did try a where clause (Where not exists). we got the correct result as the field is unique per file.

for a different requirement this may not work.

i can see multiple variations of solutions using a where clause. the issue there though is that this was an optimization exercise. using a where clause apparently loads the whole QVD (understandably QV would need to load all rows to figure which ones would satisfy the where clause). and we have QVDs with multiple million records, hence we wanted to use the first 1 load.

Re: First 1 load loading multiple rows

Mon, 20 Jul 2015 12:25:33 GMT

we tried a distinct already and it does work, though it apparently loads multiple records prior to applying the distinct - which defeats the purpose of the first 1 load. again, this was an optimization exercise.

Not sure i got the question re the variable. the only variable used is the QVD directory which points to multiple QVDs. we need this to be a variable.

i havent tested using a string in the FROM clause but that shouldnt matter. if it does then thats another question WHY.

Re: First 1 load loading multiple rows

Mon, 20 Jul 2015 12:26:19 GMT

please see my reply to the suggestion to use WHERE clause. we are optimizing the load and this defeats the purpose.

Re: First 1 load loading multiple rows

swuehl — Mon, 20 Jul 2015 12:30:55 GMT

I assume the optimized load is causing the issue with FIRST.

Have you tried a where clause with FIRST I would have assumed that it's not doing the comparison on the complete records set?

Re: First 1 load loading multiple rows

Mon, 20 Jul 2015 12:38:22 GMT

just to summarize what weve done that kinda works:

1. we added fields to the autogenerate (by accident we found that if the autogenerate (target table) had multiple fields - the target field + any other field, the FIRST 1 LOAD clause works):

Calendar2:

load '' as [KeyFiscalYearWeek2],

load '' as anotherField

AutoGenerate 0;

Concatenate(Calendar2)

first 1 load .....

2. we added calculated fields to the load statement - the FIRST 1 LOAD clause works:

Calendar2:

load '' as [KeyFiscalYearWeek2],

load '' as anotherField

AutoGenerate 0;

Concatenate(Calendar2)

first 1 load

[Fiscal Year Week] as KeyFiscalYearWeek2,

left([Fiscal Year Week],4) as [anotherField]...

3. one more thing we tested but was not successful. we also removed the rename in the the load statement:

Calendar2:

load '' as [Fiscal Year Week]

AutoGenerate 0;

Concatenate(Calendar2)

first 1 load

[Fiscal Year Week]

from $(vSourceTable_VendorSales)

(qvd);

this resulted in progressive fetches of multiple rows:

Calendar2 << AUTOGENERATE(0) 0 lines fetched

Calendar2 << 201450 (qvd optimized) 1 lines fetched

Calendar2 << 201451 (qvd optimized) 3 lines fetched

Calendar2 << 201452 (qvd optimized) 7 lines fetched

Calendar2 << 201453 (qvd optimized) 15 lines fetched

Calendar2 << 201501 (qvd optimized) 31 lines fetched

Calendar2 << 201502 (qvd optimized) 63 lines fetched

Calendar2 << 201503 (qvd optimized) 127 lines fetched

Calendar2 << 201504 (qvd optimized) 255 lines fetched

Calendar2 << 201505 (qvd optimized) 511 lines fetched

Calendar2 << 201506 (qvd optimized) 1,023 lines fetched

Calendar2 << 201507 (qvd optimized) 2,047 lines fetched

Calendar2 << 201508 (qvd optimized) 4,095 lines fetched

Re: First 1 load loading multiple rows

Mon, 20 Jul 2015 12:41:33 GMT

Hi Swuehl,

yes, weve tried the WHERE clause and it does load the whole QVD. i just tried using a WHERE recno() =1 and it loaded the whole QVD unoptimized.

Re: First 1 load loading multiple rows

swuehl — Mon, 20 Jul 2015 13:07:22 GMT

I do see something different:

20.07.2015 14:54:31

Table1 << AUTOGENERATE(50000000) 50.000.000 lines fetched

20.07.2015 14:56:57

20.07.2015 14:56:58

ALL << TestFIRST (qvd optimized) 50.000.000 lines fetched

20.07.2015 14:57:56

20.07.2015 14:57:57

WHERE << TestFIRST 1 lines fetched

20.07.2015 14:58:15

FIRST << TestFIRST 1 lines fetched

20.07.2015 14:58:16

First created 50 Mill records in the script, then stored in a QVD.

ALL: optimized LOAD of the QVD, ca. 58 seconds

WHERE recno()=1: 1 record, ca. 18 seconds

FIRST 1 ... WHERE 1=1: 1 record, ca.1 second

Re: First 1 load loading multiple rows

Mon, 20 Jul 2015 13:23:51 GMT

Hi Swuehl,

thank you for your time. adding the the where 1=1 does load 1 records per QVD. however, it appears to be slower than using other variations of the FIRST 1 LOAD we are using for now.

i "think" using a WHERE clause loads the whole QVD but i could be wrong - however, thats the only explanation i can think of why it is slow.

At the end of the day, we expect QLIK to live up to what it is advertising. IF the intent of the FIRST 1 LOAD is to load (or fetch) only 1 record (first record hopefully) then thats what it should do. design strategies are built around what is advertised and if the product deviates from that then the design foundation is built on sand.

Re: First 1 load loading multiple rows

swuehl — Mon, 20 Jul 2015 13:32:57 GMT

All I am saying is that the issue is apparently caused by the optimized load.

In my testing, I am fine with a 1 second LOAD time for a FIRST 1 in combination with WHERE 1=1 (and you don't need to drop any calculated anotherField you may only have created to make the LOAD unoptimized).

I would agree that there seems to be an issue with FIRST LOAD prefix on optimized qvd load (similar to other issues we have seen), I am not defending the behaviour. Have you created a ticket with QT support?

Regards,

Stefan

Re: First 1 load loading multiple rows

Mon, 20 Jul 2015 13:38:32 GMT

Hi Swuehl,

again, thanks for your time.

we havent forwarded this as an issue and wanted to see first if the community has experienced this and what it thinks.

as we do have an acceptable work around, we might use that prior to approaching support.