topic Re: For loop causing data to double in App Development

For loop causing data to double

BenjaminCoe1440 — Wed, 09 Nov 2022 14:11:24 GMT

ReadingsTemp seems to be duplicating my data for each loop. I've pasted my code below. There isn't an error but it seems to be stuck in an infinite loop.

Please advise!

[ClientList]: LOAD * INLINE [ AccountName, AccountId 'Company A', 'e4741f2f-#########-######' 'Company B', '4c3d8138-#########-######' ]; let vNumRows=NoOfRows('ClientList'); [MetRefTemp]: LOAD * INLINE [ AccNam,AccountId,MeterReference ]; for it=0 to $(vNumRows) let vAcc=peek('AccountId',$(it),'ClientList'); let vAccName=peek('AccountName',$(it),'ClientList'); load * resident [MetRefTemp]; Concatenate LOAD distinct D365AccName AS AccNam ,AccountId , MeterReference RESIDENT [DynamicsAccounts] WHERE (AccountId = '$(vAcc)'); next; // Get readings from meter references in MetRefTemp table let vMeterRows=NoOfRows('MetRefTemp'); [ReadingsTemp]: LOAD * INLINE [ MeterReference, ReadingsID, ReadingDateTime, RptTime, Value_Amount ]; for it = 0 to $(vMeterRows) let vMet=peek('MeterReference',$(it),'MetRefTemp'); load * resident [ReadingsTemp]; concatenate load MeterReference ,ReadingsID ,ReadingDateTime ,RptTime ,Value_Amount resident [ENMATReadings] where (MeterReference='$(vMet)'); next; left join(ReadingsTemp) load * Resident MetRefTemp; for it=0 to $(vNumRows) let vAccName=peek('AccountName',$(it),'ClientList'); [TempLoad]: NoConcatenate load * resident [ReadingsTemp] where (AccNam='$(vAccName)'); store [TempLoad] into [lib://Admin - Data Loaders:DataFiles/metersdist_$(vAccName).qvd](qvd); drop table [TempLoad]; next;

Re: For loop causing data to double

PrashantSangle — Wed, 09 Nov 2022 14:23:31 GMT

use trace command to check which no of loop is going on..

Re: For loop causing data to double

BenjaminCoe1440 — Wed, 09 Nov 2022 14:26:36 GMT

Please elaborate. I'm not sure where I would use the trace command in this context.

Re: For loop causing data to double

PrashantSangle — Wed, 09 Nov 2022 14:33:17 GMT

https://help.qlik.com/en-US/sense/August2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegularStatements/Trace.htm

check how to use trace.

so if you want to see in your script which no of loops is running or what is value of variable during load time then you can use trace.

[ClientList]:
LOAD * INLINE [
AccountName, AccountId
'Company A', 'e4741f2f-#########-######'
'Company B', '4c3d8138-#########-######'

];
let vNumRows=NoOfRows('ClientList');

trace $(vNumRows);

[MetRefTemp]:
LOAD * INLINE [
AccNam,AccountId,MeterReference
];

for it=0 to $(vNumRows)
let vAcc=peek('AccountId',$(it),'ClientList');
let vAccName=peek('AccountName',$(it),'ClientList');

trace $(it);

load *
resident [MetRefTemp];

Concatenate
LOAD distinct
D365AccName AS AccNam
,AccountId
, MeterReference
RESIDENT [DynamicsAccounts]
WHERE (AccountId = '$(vAcc)');
next;

// Get readings from meter references in MetRefTemp table
let vMeterRows=NoOfRows('MetRefTemp');

[ReadingsTemp]:
LOAD * INLINE [
MeterReference, ReadingsID, ReadingDateTime, RptTime, Value_Amount
];

for it = 0 to $(vMeterRows)
let vMet=peek('MeterReference',$(it),'MetRefTemp');

trace $(it);

load *
resident [ReadingsTemp];

concatenate

load
MeterReference
,ReadingsID
,ReadingDateTime
,RptTime
,Value_Amount
resident [ENMATReadings]
where (MeterReference='$(vMet)');
next;

left join(ReadingsTemp)
load * Resident MetRefTemp;

for it=0 to $(vNumRows)
let vAccName=peek('AccountName',$(it),'ClientList');

[TempLoad]:
NoConcatenate
load *
resident [ReadingsTemp]
where (AccNam='$(vAccName)');
store [TempLoad] into [lib://Admin - Data Loaders:DataFiles/metersdist_$(vAccName).qvd](qvd);
drop table [TempLoad];
next;

I added trace in most of place. You can modify as per your need.

Regards,

Prashant Sangle

Re: For loop causing data to double

marcus_sommer — Wed, 09 Nov 2022 15:06:38 GMT

There are multiple issues within your approach, for example running to often through the loop because noofrows('ClientList') is 2 but your loop goes from 0 to 2 which are 3 iterations (it will later cause an empty variable and load-errors).

More important is that you didn't specify to which table you want to apply the concatenate - it should be always explicitly set like you did by the join, means: concatenate(TableName) because without it the next table above is taken and I think that's the wrong one.

- Marcus

Re: For loop causing data to double

BenjaminCoe1440 — Thu, 10 Nov 2022 10:34:17 GMT

Thank Marcus.

Right, so, issue No1:

noofrows('ClientList') is 2 but your loop goes from 0 to 2 which are 3 iterations (it will later cause an empty variable and load-errors

Using for it=0 to $(vNumRows) would give me 3 iterations, so would for it=1 to $(vNumRows) give me the required 2 rows?

Or should it be something more like for it=0 to $(vNumRows)-1 , for example?

Issue no2:

you didn't specify to which table you want to apply the concatenate - it should be always explicitly set like you did by the join, means: concatenate(TableName) because without it the next table above is taken and I think that's the wrong one.

Unfortunately, the suggested solution didn't fix my issue. I still have a loop which doubles the number of lines for each iteration. vMeterRows has 95 rows to iterate over. I think I've isolated the bug to this loop.

What I'm trying to do is use the field MeterReference to identify the name of the company (AccName) for that individual meter (left join at the bottom). I can then use the AccountName to split into qvd files of readings for that company.

MetRefTemp table

AccNam	AccountId	MeterReference
Company A	e4741f2f-#########-######	METER A
Company A	e4741f2f-#########-######	METER B
...	...	...
Company B	4c3d8138-#########-######	METER ZZZZ

What ENMATReadings is pulling through is;

MeterReference	ReadingsID	ReadingDateTime	RptTime	Value_Amount (kWh)
METER A	0001A	10/10/2022 09:00	09:00	95461.05
METER B	0002A	10/10/2022 09:00	09:00	95462
...	...	...	...	...
METER ZZZZ	B0001T	11/10/2022 09:30	09:30	94111

// Get readings from meter references in MetRefTemp table let vMeterRows=NoOfRows('MetRefTemp'); [ReadingsTemp]: LOAD * INLINE [ MeterReference, ReadingsID, ReadingDateTime, RptTime, Value_Amount ]; for it = 0 to $(vMeterRows) let vMet=peek('MeterReference',$(it),'MetRefTemp'); trace $(it); load * resident [ReadingsTemp]; concatenate(ReadingsTemp) load MeterReference ,ReadingsID ,ReadingDateTime ,RptTime ,Value_Amount resident [ENMATReadings] where (MeterReference='$(vMet)'); next; left join(ReadingsTemp) load * Resident MetRefTemp;

Re: For loop causing data to double

vinieme12 — Fri, 11 Nov 2022 04:55:02 GMT

You are actually loading the Existing table for each row

[ClientList]:
LOAD * INLINE [
AccountName, AccountId
'Company A', 'e4741f2f-#########-######'
'Company B', '4c3d8138-#########-######'

];
let vNumRows=NoOfRows('ClientList');

[MetRefTemp]:
LOAD * INLINE [
AccNam,AccountId,MeterReference
];

for it=0 to $(vNumRows)
let vAcc=peek('AccountId',$(it),'ClientList');
let vAccName=peek('AccountName',$(it),'ClientList');

//*

This block is causing the duplication,

You are loading the same table you want to concatenate the data to within the loop!!

this is what is happening

at n = 0 You load filter companyA and append to empty table [MetRefTemp ]

when n =1, load resident [MetRefTemp ] which already has companyA and Concatenate companyB

now MetRefTemp companyA + (n=1 companyA+companyB)

when n =2

now MetRefTemp companyA + ( companyA + (n=1 companyA+companyB) ) + companyC

and so on

*//

load *
resident [MetRefTemp];

Concatenate(MetRefTemp)
LOAD distinct
D365AccName AS AccNam
,AccountId
, MeterReference
RESIDENT [DynamicsAccounts]
WHERE (AccountId = '$(vAcc)');
next;

// Get readings from meter references in MetRefTemp table
let vMeterRows=NoOfRows('MetRefTemp');

[ReadingsTemp]:
LOAD * INLINE [
MeterReference, ReadingsID, ReadingDateTime, RptTime, Value_Amount
];

for it = 0 to $(vMeterRows)
let vMet=peek('MeterReference',$(it),'MetRefTemp');

load *
~~resident [ReadingsTemp];~~

concatenate(ReadingsTemp)

load
MeterReference
,ReadingsID
,ReadingDateTime
,RptTime
,Value_Amount
resident [ENMATReadings]
where (MeterReference='$(vMet)');
next;

left join(ReadingsTemp)
load * Resident MetRefTemp;

for it=0 to $(vNumRows)
let vAccName=peek('AccountName',$(it),'ClientList');

Re: For loop causing data to double

marcus_sommer — Fri, 11 Nov 2022 08:41:12 GMT

The loop should in your case be defined with:

for it=0 to $(vNumRows)-1

because the peek-index to access any rows starts by 0. For the cause with the duplicates look for the hints from @vinieme12.

- Marcus

Re: For loop causing data to double

BenjaminCoe1440 — Fri, 11 Nov 2022 11:25:53 GMT

Thanks again - something so simple, yet easy to overlook!

Do you know what is going on here?? Trying to store a file name with underscores instead of spaces for the customer name

let vFileAccName=lower(replace($(vAccName),' ','_'));

store [TempLoad_$(vAccName)] into [lib://Admin - Data Loaders:DataFiles/metersdist_$(vFileAccName).qvd](qvd);

The following error occurred:

Unexpected token: 'UK', expected one of: ',', ')', ':', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', ...

The error occurred here:

let vFileAccName=lower(replace(A>>>>>>UK<<<<<< Limited,'','_'))

Cheers

-Ben

Re: For loop causing data to double

marcus_sommer — Fri, 11 Nov 2022 11:29:11 GMT

The variable-content is a string and therefore the call of the variable needs to be wrapped with single-quotes, like:

let vFileAccName=lower(replace('$(vAccName)' ,' ','_'));

- Marcus