Qlik Sense loads data from QVD but considers the table empty for further operations

[Quent_3814]

Hello, I am new to Qlik and recently started working on an API query project. To summarize, my project involves retrieving data from a curve between two dates, and I would like to save this data into a QVD file that I can later load at the end of my script to analyze the data.

The code asks the user to enter their credentials, which works fine. The user can choose between two modes:

1. RESET = 1: In this mode, the script requests data from the earliest date of the curve up to today and saves everything to the QVD file. This mode works perfectly.

2. RESET = 0: This mode is causing some issues. When the script runs, it should load the existing QVD file and retrieve the last date from it. Then, it uses this date to request data from that point until today, in order to avoid reloading already existing data.

The problem is that the table OldData is considered empty, so I cannot concatenate the new data with the existing data. I have already verified that the table does contain data, but Qlik does not recognize it, preventing the concatenation :

// ============================================================
// PARAMÈTRES À RENSEIGNER
// ============================================================

// --- Paramètres Ikologik API ---
LET vCustomer = '#######'; // Customer Tag
LET vInstallation = '######'; // Installation Tag
LET vTag = '#######'; // Graph Tag
LET vDataType = 'DATA'; // Type de données (DATA, ALARM, etc.)

// --- Date de début à modifier par l'utilisateur (format : jj/mm/aa hh:mm) ---
LET vUserDate = 'DD/MM/YY hh:mm';

// --- Nom personnalisé de la variable observée (ex : Température (C°)) ---
LET vObservationLabel = 'Température (C°)';

// RESET de la BDD QVD
LET RESET = 0; // 1= executer le STORE QVD ; 0= ignorer le STORE

// ============================================================
// 1. Rafraîchissement du token via le refreshToken
// ============================================================
LIB CONNECT TO 'ikologik_API_login_acces_token (eco_qlik-user-pro-erp)';

LET Query = '{""token"":""$(vRefreshToken)""}';

RefreshTokenTable:
SQL SELECT
"refreshToken",
"accessToken"
FROM JSON (wrap on) "root"
WITH CONNECTION (
URL "https://api.ikologik.com/api/v2/auth/refresh",
HTTPHEADER "Content-Type" "application/json",
BODY "$(Query)"
);

LET vRefreshToken = Peek('refreshToken', 0, 'RefreshTokenTable');
LET vToken = Peek('accessToken', 0, 'RefreshTokenTable');
DROP TABLE RefreshTokenTable;

// ============================================================
// 2. Conversion de la date utilisateur avec prise en compte heure d'été/hiver
// ============================================================
TempUserDate:
LOAD
Timestamp(
Timestamp#('$(vUserDate)', 'DD/MM/YY hh:mm')
- IF(
Date(Floor(Timestamp#('$(vUserDate)', 'DD/MM/YY hh:mm'))) >=
MakeDate(Year(Timestamp#('$(vUserDate)', 'DD/MM/YY hh:mm')), 3, 31)
- WeekDay(MakeDate(Year(Timestamp#('$(vUserDate)', 'DD/MM/YY hh:mm')), 3, 31), 0)
AND
Date(Floor(Timestamp#('$(vUserDate)', 'DD/MM/YY hh:mm'))) <
MakeDate(Year(Timestamp#('$(vUserDate)', 'DD/MM/YY hh:mm')), 10, 31)
- WeekDay(MakeDate(Year(Timestamp#('$(vUserDate)', 'DD/MM/YY hh:mm')), 10, 31), 0),
2/24, // Été : -2h
1/24 // Hiver : -1h
)
) AS LocalInput
AUTOGENERATE 1;

LET vStartQlikTimestamp = Peek('LocalInput', 0, 'TempUserDate');
LET vEndQlikTimestamp = Now();
DROP TABLE TempUserDate;

// ============================================================
// 2b. Gestion du QVD existant (si RESET = 0)
// ============================================================
LET vSafeObservationLabel = Replace('$(vObservationLabel)', ' ', '_');
LET vSafeObservationLabel = Replace('$(vSafeObservationLabel)', '(', '');
LET vSafeObservationLabel = Replace('$(vSafeObservationLabel)', ')', '');
LET vSafeObservationLabel = Replace('$(vSafeObservationLabel)', '°', 'deg');

LET vQvdFilePath = 'lib://QVDs_Quentin (eco_qlik-user-pro-erp)/' & '$(vSafeObservationLabel)' & '.qvd';

IF $(RESET) = 0 THEN
IF FileSize('$(vQvdFilePath)') > 0 THEN

TempQvdData:
LOAD *
FROM [$(vQvdFilePath)] (qvd);

// Lecture de la dernière date au format texte dans le QVD
LET vLastDateRaw = Peek('LocalTime_$(vObservationLabel)', -1, 'TempQvdData');
Trace Derniere date dans le QVD : $(vLastDateRaw);

DROP TABLE TempQvdData;

TempLastDateCalc:
LOAD
// On récupère la dernière date du QVD en heure locale
Timestamp#('$(vLastDateRaw)', 'DD/MM/YYYY hh:mm:ss') AS LocalTime,
// Conversion en UTC en retirant le décalage été/hiver
Timestamp#('$(vLastDateRaw)', 'DD/MM/YYYY hh:mm:ss')
- IF(
Date(Floor(Timestamp#('$(vLastDateRaw)', 'DD/MM/YYYY hh:mm:ss'))) >=
MakeDate(Year(Timestamp#('$(vLastDateRaw)', 'DD/MM/YYYY hh:mm:ss')), 3, 31)
- WeekDay(MakeDate(Year(Timestamp#('$(vLastDateRaw)', 'DD/MM/YYYY hh:mm:ss')), 3, 31), 0)
AND
Date(Floor(Timestamp#('$(vLastDateRaw)', 'DD/MM/YYYY hh:mm:ss'))) <
MakeDate(Year(Timestamp#('$(vLastDateRaw)', 'DD/MM/YYYY hh:mm:ss')), 10, 31)
- WeekDay(MakeDate(Year(Timestamp#('$(vLastDateRaw)', 'DD/MM/YYYY hh:mm:ss')), 10, 31), 0),
2/24, // été
1/24 // hiver
)
+ 5/1440 // +5 minutes pour la prochaine valeur
AS UTC_Input
AUTOGENERATE 1;

LET vStartQlikTimestamp = Peek('UTC_Input', 0, 'TempLastDateCalc');

DROP TABLE TempLastDateCalc;

TRACE vStartQlikTimestamp UTC calculé : $(=Date($(vStartQlikTimestamp), 'DD/MM/YYYY')) $(=Time($(vStartQlikTimestamp), 'hh:mm:ss'));

ELSE
TRACE Fichier QVD introuvable ou vide : $(vQvdFilePath);
ENDIF
ENDIF

// ============================================================
// 3. Boucle de récupération des données par tranche de 6 mois
// ============================================================
LET vCurrentDate = vStartQlikTimestamp;

FOR i = 1 TO 100

LET vFromDate = Floor((vCurrentDate - MakeDate(1970)) * 86400000);
LET vNextDate = AddMonths(vCurrentDate, 6);
IF vNextDate > vEndQlikTimestamp THEN
LET vNextDate = vEndQlikTimestamp;
ENDIF
LET vToDate = Floor((vNextDate - MakeDate(1970)) * 86400000);

LET vUrl =
'https://api.ikologik.com/api/v2/customer/' & '$(vCustomer)' &
'/installation/' & '$(vInstallation)' &
'/graphmeter/' & '$(vTag)' &
'/graph/data/' & '$(vDataType)' &
'/' & '$(vFromDate)' & '/' & '$(vToDate)' &
'?autoReduce=false&limit=1000000';

LIB CONNECT TO 'ikologik_GET_Data (eco_qlik-user-pro-erp)';

RestConnectorMasterTable:
SQL SELECT
"__KEY_root",
(SELECT "@Value", "__FK_root"
FROM "root" FK "__FK_root" ArrayValueAlias "@Value")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (
URL "$(vUrl)",
HTTPHEADER "Authorization" "Bearer $(vToken)"
);

[root_$(i)]:
LOAD [@Value], [__FK_root] AS [__KEY_root], '$(i)' AS BatchID
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_root]);

DROP TABLE RestConnectorMasterTable;

LET vCurrentDate = vNextDate;

EXIT FOR WHEN vCurrentDate >= vEndQlikTimestamp;

NEXT i;

// ============================================================
// 4. Fusion des données collectées
// ============================================================
CombinedRaw:
LOAD [@Value], RowNo() AS RowNum
RESIDENT [root_1];
DROP TABLE [root_1];

FOR j = 2 TO $(i)
IF TableNumber('root_$(j)') > 0 THEN
CONCATENATE (CombinedRaw)
LOAD [@Value], RowNo() AS RowNum
RESIDENT [root_$(j)];
DROP TABLE [root_$(j)];
ENDIF
NEXT j;

// ============================================================
// 5. Séparation temps / observation
// ============================================================
Times:
LOAD [@Value] AS Time, Floor(RowNum / 2) + 1 AS PairID
RESIDENT CombinedRaw
WHERE Mod(RowNum, 2) = 1;

Observations:
LOAD [@Value] AS [$(vObservationLabel)], Floor((RowNum + 1) / 2) AS PairID
RESIDENT CombinedRaw
WHERE Mod(RowNum, 2) = 0;

DROP TABLE CombinedRaw;

// ============================================================
// 6. Fusion temps + observation
// ============================================================
FinalData:
JOIN (Times)
LOAD PairID, [$(vObservationLabel)]
RESIDENT Observations;

DROP TABLE Observations;

// DROP conditionnel de FinalData (évite erreur si table inexistante)
IF TableNumber('FinalData') > 0 THEN
DROP TABLE FinalData;
ENDIF

// ATTENTION : ici on vient de DROP FinalData,
// la table Times existe encore et sera utilisée dans l'étape suivante

// ============================================================
// 7. Conversion des timestamps UTC → Heure locale (Bruxelles)
// ============================================================
FinalData_LocalTime:
LOAD
Timestamp(
([Time] / 1000) / 86400 + MakeDate(1970) +
If(
Date(Floor(([Time] / 1000) / 86400 + MakeDate(1970))) >=
MakeDate(Year(Floor(([Time] / 1000) / 86400 + MakeDate(1970))), 3, 31) -
WeekDay(MakeDate(Year(Floor(([Time] / 1000) / 86400 + MakeDate(1970))), 3, 31), 0)
and
Date(Floor(([Time] / 1000) / 86400 + MakeDate(1970))) <
MakeDate(Year(Floor(([Time] / 1000) / 86400 + MakeDate(1970))), 10, 31) -
WeekDay(MakeDate(Year(Floor(([Time] / 1000) / 86400 + MakeDate(1970))), 10, 31), 0),
2/24,
1/24
)
) AS [LocalTime_$(vObservationLabel)],
Replace([$(vObservationLabel)], '.', ',') AS [$(vObservationLabel)]
RESIDENT Times;

DROP TABLE Times;

// ============================================================
// 8. Création du chemin QVD avec nom basé sur vObservationLabel (nettoyé)
// ============================================================

IF $(RESET) = 0 THEN

OldData:
LOAD *
FROM [$(vQvdFilePath)] (qvd);

// Lecture de la dernière date au format texte dans le QVD

// Concaténation seulement si OldData et FinalData_LocalTime existent

CONCATENATE (OldData)
LOAD *
RESIDENT FinalData_LocalTime;

STORE OldData INTO [$(vQvdFilePath)] (qvd);
LET vLastDateRaw2 = Peek('LocalTime_$(vObservationLabel)', -1, 'OldData');
Trace Derniere date dans le QVD : $(vLastDateRaw2);

DROP TABLE OldData;
DROP TABLE FinalData_LocalTime;

ELSE

STORE FinalData_LocalTime INTO [$(vQvdFilePath)] (qvd);

DROP TABLE FinalData_LocalTime;

ENDIF

// ============================================================
// 9. Chargement des données depuis le QVD pour analyse
// ============================================================

DataFromQVD:
LOAD *
FROM [$(vQvdFilePath)] (qvd);

[robert_mika]

Can you translate to English all the 9 points first?