Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
FMDF
Contributor III
Contributor III

Loading Data in a For loop

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.

 

 

Labels (5)
10 Replies
lorenzoconforti
Specialist II
Specialist II

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

 

tCal:

NoConcatenate Load

 

 

FMDF
Contributor III
Contributor III
Author

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.

FMDF
Contributor III
Contributor III
Author

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.

lorenzoconforti
Specialist II
Specialist II

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?

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Please post the script log.

-Rob

FMDF
Contributor III
Contributor III
Author

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.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

-Rob

FMDF
Contributor III
Contributor III
Author

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 🙂

 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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