First 1 load loading multiple rows

[#1]

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.

[#2]

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

[ramoncova06]

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

[swuehl]

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;

[#5]

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.

[#6]

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.

[#7]

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

[swuehl]

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?

[#9]

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

[#10]

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.