Solved: Re: For loop causing data to double - Qlik Community

BenjaminCoe1440 · ‎2022-11-09

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;

vinieme12 · ‎2022-11-10

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');

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

PrashantSangle · ‎2022-11-09

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

BenjaminCoe1440 · ‎2022-11-09

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

PrashantSangle · ‎2022-11-09

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

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

marcus_sommer · ‎2022-11-09

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

BenjaminCoe1440 · ‎2022-11-10

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;

vinieme12 · ‎2022-11-10

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');

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

marcus_sommer · ‎2022-11-11

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

BenjaminCoe1440 · ‎2022-11-11

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

marcus_sommer · ‎2022-11-11

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

For loop causing data to double

SaaS