Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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.
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
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.