Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
use trace command to check which no of loop is going on..
Please elaborate. I'm not sure where I would use the trace command in this context.
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
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
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;
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;
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
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);
Cheers
-Ben
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