topic Re: Loading Data in a For loop in App Development

Loading Data in a For loop

FMDF — Sat, 16 Nov 2024 19:07:12 GMT

I have order records that need to map on a chart. There is a start and end date for each order line. I need to create a row in a DateLink table for each month that the order line is open.

In my master calendar, I have a flag that tells me what the first day of the fiscal month is.

I only want to include the actual start date and then all "first day of fiscal month" values for each month. This will allow me to map how I want later.

The first time through the loop IT WORKS. The second time it fails with an error message that makes zero sense based on the code.

NoConcatenate OrdTemp:
Load
*
Resident Order_Temp4
WHERE IsNull(startDateEffective) = 0;

//Get the count of rows
LET numOrderRows = NoOfRows('OrdTemp');

//Loop through all the order rows
FOR i = 0 to $(numOrderRows)-1
Let vUniqueId = Peek('uniqueId',$(i),'OrdTemp');
Let vStartDate = Num(Date(Floor(Peek('startDateEffective',$(i),'OrdTemp'))));
Let vEndDate = Num(Date(Floor(Peek('endDateEffective',$(i),'OrdTemp'))));

NoConcatenate tCal: /////THIS IS THE LINE THAT FAILS THE 2ND TIME THRU THE LOOP. ERROR BELOW.
Load
DISTINCT
masterCalDateKey
Resident DIM_MasterCalendar
Where
masterCalDateKey = $(vStartDate) OR //Need to include the actual startDateEffective
(masterCalDateKey > $(vStartDate) AND masterCalDateKey <= $(vEndDate) AND masterCalFirstDayofFiscalMonthFlag = 1) //Need to include all the month beginning dates
;

//Get the row count for the tCal table
LET numCalRows = NoOfRows('tCal');

//Loop thru the calendar records
FOR j = 0 to $(numCalRows)-1

//Get the next date key to add to the date link table.
vMasterCalDate = Peek('masterCalDateKey',$(j),'tCal');

Concatenate(MasterDateLink)
LOAD
$(vUniqueId) AS uniqueId,
$(vMasterCalDate) AS masterCalDateKey,
'Start Date Effective' AS dateType
;
Next;

Drop Table tCal;
Next;

This is the error:

Field 'USOrderDetail' not found: tCal1: Load DISTINCT masterCalDateKey Resident DIM_MasterCalendar Where masterCalDateKey = 38966 OR (masterCalDateKey > 38966 AND masterCalDateKey <= 39247 AND masterCalFirstDayofFiscalMonthFlag = 1) //Need to include all the month beginning dates

I don't even reference a field called "USOrderDetail" in this loop. Ever. I am completely stuck.

Re: Loading Data in a For loop

lorenzoconforti — Thu, 13 Feb 2020 16:28:40 GMT

I believe NoConcatenate should go before the load statement, not the table name

tCal:

NoConcatenate Load

Re: Loading Data in a For loop

FMDF — Thu, 13 Feb 2020 16:31:43 GMT

I have NoConcatenate in front of the table name all over my scripts and they work fine.

The first time through the loop it works.

It's the second time through that it is throwing that nonsense error.

Re: Loading Data in a For loop

FMDF — Thu, 13 Feb 2020 16:55:20 GMT

Moving the NoConcatenate after the table name did not solve the problem. Same error is coming up.

There is no logic to not finding a column name that isn't even referenced.

Re: Loading Data in a For loop

lorenzoconforti — Thu, 13 Feb 2020 17:04:33 GMT

it does look strange

Are you sure that during the first pass the script works as expected? I understand it doesn't give error, but does it produce what you are looking for during the first pass?

Can you share this application? Can you post the a picture of the Data Model Viewer?

Re: Loading Data in a For loop

rwunderlich — Thu, 13 Feb 2020 18:11:24 GMT

Please post the script log.

-Rob

Re: Loading Data in a For loop

FMDF — Thu, 13 Feb 2020 18:51:10 GMT

Actually, I wrote the data out to a qvd to debug.

It is not doing the Concatenate to MasterDateLink at all on the first pass...it just doesn't post an error.

Re: Loading Data in a For loop

rwunderlich — Fri, 14 Feb 2020 01:09:42 GMT

That makes sense as there is no data source in that Load statement.

-Rob

Re: Loading Data in a For loop

FMDF — Fri, 14 Feb 2020 03:02:58 GMT

There doesn't need to be a data source in the load statement. I was missing the AutoGenerate 1 at the end and I was missing single quotes around the first column. So it took the data as a column name instead of a literal.

I will post the correction tomorrow since it may help others in the future. While I believe it works now, the performance is awful. I will have to think of a new approach.

Cheers 🙂

Re: Loading Data in a For loop

rwunderlich — Fri, 14 Feb 2020 03:28:04 GMT

AutoGenerate is a data source 😉

Looping over the rows of a table is generally very slow. You might do better with something like:

Keys:
LOAD
OrderId,
masterCalDateKey
Where masterCalDateKey = StartDate
or Lookup('masterCalFirstDayofFiscalMonthFlag', 'masterCalDateKey', masterCalDateKey, 'Calendar') = 1
;
LOAD
OrderId,
StartDate,
Date(StartDate + IterNo() - 1) as masterCalDateKey
Resident Orders
While StartDate + IterNo() - 1 <= EndDate
;

Lookup() can be kinda slow as well. If it's the case that it's too slow, you can replace the lookup with an ApplyMap like this:

MapFirstDay:
Mapping
LOAD masterCalDateKey,
masterCalFirstDayofFiscalMonthFlag
Resident Calendar
Where masterCalFirstDayofFiscalMonthFlag = 1
;
Keys:
LOAD
OrderId,
masterCalDateKey
Where masterCalDateKey = StartDate
or ApplyMap('MapFirstDay', masterCalDateKey) = 1
;
LOAD
OrderId,
StartDate,
Date(StartDate + IterNo() - 1) as masterCalDateKey
Resident Orders
While StartDate + IterNo() - 1 <= EndDate
;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Loading Data in a For loop

FMDF — Mon, 17 Feb 2020 16:14:49 GMT

I modified my original loop after noticing a flaw where the inner loop was not needed. This runs but is still so slow as to be unusable.

//No need to link records if there is no startDateEffective.
OrdTemp:
NoConcatenate
Load
*
Resident Order_Temp4
WHERE IsNull(startDateEffective) = 0;

//Get the count of rows
LET numOrderRows = NoOfRows('OrdTemp');

//Loop through all the order rows
FOR i = 0 to $(numOrderRows)-1

Let vUniqueId = Peek('uniqueId',$(i),'OrdTemp');
Let vStartDate = Num(Date(Floor(Peek('startDateEffective',$(i),'OrdTemp'))));
Let vEndDate = Num(Date(Floor(Peek('endDateEffective',$(i),'OrdTemp'))));

IF $(i)=0 THEN
   NoConcatenate tCal:
   Load
      DISTINCT
      masterCalDateKey,
      '$(vUniqueId)' AS uniqueId,
      'Start Date Effective' AS dateType
      Resident DIM_MasterCalendar
   Where
      masterCalDateKey = $(vStartDate) OR //Need to include the actual startDateEffective
      (masterCalDateKey > $(vStartDate) AND masterCalDateKey <= $(vEndDate) AND masterCalFirstDayofFiscalMonthFlag = 1) //Need to include all the month beginning dates
   ;
ELSE
   Concatenate(tCal)
   Load
      DISTINCT
      masterCalDateKey,
      '$(vUniqueId)' AS uniqueId,
      'Start Date Effective' AS dateType
   Resident DIM_MasterCalendar
   Where
      masterCalDateKey = $(vStartDate) OR //Need to include the actual startDateEffective
      (masterCalDateKey > $(vStartDate) AND masterCalDateKey <= $(vEndDate) AND masterCalFirstDayofFiscalMonthFlag = 1) //Need to include all the month beginning dates
   ;
End If

//Get the row count for the tCal table
//LET numCalRows = NoOfRows('tCal');

Next;

Concatenate(MasterDateLink)
LOAD
uniqueId,
masterCalDateKey,
dateType
Resident tCal;

Drop Table tCal;