topic Re: syntax error in my load script in App Development

syntax error in my load script

darrinturner — Fri, 02 Apr 2021 08:31:48 GMT

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:

Re: syntax error in my load script

Saravanan_Desingh — Tue, 07 Jul 2020 03:13:25 GMT

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.

Re: syntax error in my load script

marcus_sommer — Wed, 08 Jul 2020 16:07:54 GMT

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

Re: syntax error in my load script

darrinturner — Wed, 08 Jul 2020 17:41:20 GMT

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.

Re: syntax error in my load script

marcus_sommer — Thu, 09 Jul 2020 07:59:29 GMT

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

Re: syntax error in my load script

darrinturner — Thu, 09 Jul 2020 12:39:33 GMT

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.