The control statement is not correctly matched with its corresponding start statement

[Jay-C]

Hi Qlik Community

Trialing out QLIK and have run into an syntax error that I don't seem to be able to work out when using the 'Data Load Editor'.

Attempting to limit data import for a particular date range only if the "createdAt" column exists. If it doesn't exist, just import the entire dataset. TableNames are listed at the top of the query.

I did force an exit script using /* however this isn't resolving the underlying issue in the code.

Thoughts and help greatly appreciated 🙂

Here's an extract of the script:

[Tables to load]:
LOAD * INLINE [
TableName
CostCentres
UserActions
];

LIB CONNECT TO '<<REMOVED>>';

FOR EACH vTable in FieldValueList('TableName')
[$(vTable)]:
// Check if 'createdAt' field exists in the table
LET vFieldExists = NoOfFields('createdAt') > 0;

// Load data based on whether 'createdAt' exists and date criteria
IF $(vFieldExists) THEN
SELECT *
FROM [bustle-supply].$(vTable)
WHERE "createdAt" >= '2022-06-01T00:00:00Z' AND "createdAt" <= '2022-06-30T23:59:59Z'
OR "createdAt" >= '2023-06-01T00:00:00Z' AND "createdAt" <= '2023-06-30T23:59:59Z';
ELSE
SELECT *
FROM [bustle-supply].$(vTable);
END IF;

// Store data in QVD
STORE [$(vTable)] into [lib://DataFiles/$(vTable).qvd];

NEXT vTable

EXIT script;

[maxgro]

I tried this and it works. 

Try replacing

LET vFieldExists = NoOfFields('createdAt') > 0;

with

LET vFieldExists = FieldNumber('createdAt', '$(vTable)') > 0;

CostCentres:
LOAD * inline [
field1, field2
1,1
2,2
];

UserActions:
LOAD * inline [
field1, field2, createdAt
1,1, 2022-06-01T00:00:00Z
2,2, 2021-06-01T00:00:00Z
];

[Tables to load]:
LOAD * INLINE [
TableName
CostCentres
UserActions
];

//LIB CONNECT TO '<<REMOVED>>';

FOR EACH vTable in FieldValueList('TableName')

trace vTable=$(vTable);

[$(vTable)]:
// Check if 'createdAt' field exists in the table
LET vFieldExists = FieldNumber('createdAt','$(vTable)') > 0;
trace vFieldExists=$(vFieldExists);

// Load data based on whether 'createdAt' exists and date criteria
IF $(vFieldExists) THEN
trace 'YES';
LOAD *
RESIDENT $(vTable)
WHERE "createdAt" >= '2022-06-01T00:00:00Z' AND "createdAt" <= '2022-06-30T23:59:59Z'
OR "createdAt" >= '2023-06-01T00:00:00Z' AND "createdAt" <= '2023-06-30T23:59:59Z';
ELSE
trace 'NO';
LOAD *
RESIDENT $(vTable);
END IF;

//// Store data in QVD
//STORE [$(vTable)] into [lib://DataFiles/$(vTable).qvd];

NEXT vTable

EXIT script;

[Jay-C · Accepted solution]

Thanks Maxgro...

Thanks for taking the time. I couldn't quite get it to work as seamlessly for 151 tables... but editing this further I found the following solution ended up working well...

[Tables to load]:

LOAD * INLINE [

    TableName

X

X1

X2

X3

];

LIB CONNECT TO '<<REMOVED>>';

FOR EACH vTable in FieldValueList('TableName')

[$(vTable)]:

   SELECT * FROM [bustle-supply].$(vTable) where 1=2;

    // Check if 'createdAt' field exists in the table

    LET vFieldExists = FieldNumber('createdAt','$(vTable)');

    // trace vFieldExists=$(vFieldExists);

    // Load data based on whether 'createdAt' exists and date criteria

    IF (vFieldExists > 0) THEN

        SELECT *

        FROM [bustle-supply].$(vTable)

        WHERE (createdAt >= '2022-06-01' AND createdAt <= '2022-06-30')

        OR (createdAt >= '2023-06-01' AND createdAt <= '2023-06-30');

    ELSE

        SELECT *

        FROM [bustle-supply].$(vTable);

    END IF;

    // Store data in QVD

    STORE [$(vTable)] into [lib://DataFiles/$(vTable).qvd];

NEXT vTable;

exit script;