syntax error in my load script

[darrinturner]

Hi all,

I am posting this again as my first post was marked as spam. 

I have a load script the is getting a syntax error, but I can not find it. I am asking the community to see if they might be able to spot it.

The Script:

$(vAssociationTableName):
LOAD * INLINE [
_FactCounter
1
] WHERE 1 = 0;

LET vNoOfTables = NoOfTables();
FOR vTableNumber = 0 to ($(vNoOfTables) -1)
LET vTableName = TableName($(vTableNumber));
LET vTempTableName = '$(vTableName)';
IF Right('$(vTableName)',7) = '_NOLOAD' THEN
LET vTempTableName = Left('$(vTableName)',(Len('$(vTableName)')-7));
END IF
LET vFactFieldName = '_Fact_' & '$(vTempTableName)' & 'Key';
IF FieldNumber('$(vFactFieldName)','$(vTableName)') > 0 THEN
LET vLoadString = 'CONCATENATE (' & '$(vAssociationTableName)' & ') ' & Chr(13) & Chr(10) & 'LOAD DISTINCT ' & '$(vFactFieldName)' & Chr(13) & Chr(10);
LET vNoOfFields = NoOfFields('$(vTableName)');
FOR vFieldNumber = 1 to $(vNoOfFields)
LET vFieldName = FieldName($(vFieldNumber), '$(vTableName)');
IF Left('$(vFieldName)',1) = '_' AND Right('$(vFieldName)',2) = 'ID' AND Left('$(vFieldName)',6) <> '_Fact_' THEN
LET vStringStart = Index('$(vFieldName)','_',-1);
LET vStringEnd = (Len('$(vFieldName)') - 2);
LET vStringLen = $(vStringEnd) - $(vStringStart);
LET vThisField = Mid('$(vFieldName)', ($(vStringStart)+1), $(vStringLen));
LET vThisFactField = '_Fact_' & '$(vThisField)' & 'Key';
IF '$(vThisFactField)' <> '$(vFactFieldName)' AND ISNULL(TableNumber('$(vThisField)')) = 0 THEN
LET vLoadString = '$(vLoadString)' & ',' & '$(vFieldName)' & ' AS ' & '$(vThisFactField)' & Chr(13) & Chr(10);
END IF
END IF
NEXT ;
LET vLoadString = '$(vLoadString)' & ', ' & 'TableName($(vTableNumber))' & ' AS _FactType' & Chr(13) & Chr(10);
LET vLoadString = '$(vLoadString)' & ',1 AS _FactCounter' & Chr(13) & Chr(10);
LET vLoadString = '$(vLoadString)' & 'RESIDENT ' & '$(vTableName)' & ';' ;
$(vLoadString)
End If
NEXT ;

Thank you in advance for your help. If this is not a post that is acceptable, please let me know before marking as spam.

Image of the syntax error:

[Saravanan_Desingh]

You didn't show the error message here. It looks like the LOAD statement is dynamically created in the variable vLoadString. You have to check if there is any syntax error.

[marcus_sommer]

Just trace your variable $(vLoadString) by ignoring the load-error in the document-log or in a textbox. Copy & paste it then into another application to see if the syntax-highlighting shows something obvious and if not run the script (maybe partial by commenting this and that). It shouldn't be too difficult to find the reason with it.

Beside this I noticed that there are no quotes or brackets for field/table-names with spaces or special-chars, like:

"Field 1" or [XX+YY]

If your fields/tables contain these spaces / special chars you need to add them with your logic.

- Marcus

[darrinturner]

Thank you for the reply.

I copied the $(vLoadString) and it looks like:

Concatenate (AssociationTable)

Load Distinct _Fact_OrganUnitsKey

,_OrganUnits_CalendarID AS _Fact_CalendarKey

,TableName(0) AS _FactType

,1 AS _FactCounter

Resident OrganUnits

Which looks good. My only concern is the TableName(0). When I look in Resident AssociationTable I see the expected table installed.

So I did the next step and that was commenting out code until the red exclamation point went away. What I found is when I commented out the $(vLoadString)  the red exclamation point did exactly that. I am stumped on this one. In Qlik View, I have this code stored in a xml file and I just call that file. It works perfectly there.

[marcus_sommer · Accepted solution]

You mean the same load-on-the-fly routine and/or this single load-statement worked in QlikView and not in Sense?

What do you mean with you stored it within a xml and called it then? Really xml or a flat-file which is called as include-variable?

Beside this the display of an error doesn't mandatory mean that there is really an error. The syntax-checker is a good tool to detect mistakes but it's not without failures - and such variables won't be parsed from it and therefore it shows often an error.

Further happens the error in each iteration of your loop or only by certain ones?

- Marcus

[darrinturner]

Great questions.

The way I am using the code is different between QV and QS. In QV, yes it is in a flat file which I call using the include statements. In QS, I have this code written directly in the data load editor. Forget I mentioned xml, I had another project in my head when I was writing. That project does basically the same thing, but it gets it's tables from and xml file. 

Yes, I am not going to call this an error again. I am agreeing with your statement " The syntax-checker is a good tool to detect mistakes but it's not without failures - and such variables won't be parsed from it and therefore it shows often an error" 

The code does exactly as needed, however I have developers that always point out the red exclamation point!

Thank you for your responses.