Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI
I am pulling the last of my hair out of my head because of this.
Hope you can help me figure out what is wrong.
I am creating a QVD file with this.
Heat_meter_qvd: LOAD Num(@1) as Cme_no, Num(@2) as Meter_no, date(@3, 'YYYY-MM-DD hh:mm') as Created, num(@8/1000) as Energy_wH, Num(@9) as Volume_m3, Num(@10) as Volume_flow_m3H, Num(@12) as Flow_temp_C, Num(@13) as Return_temp_C, num(@12-@13) as Temp_diff_C FROM [D:\HeatMeter\*.csv] (txt, codepage is 28591, no labels, delimiter is ';', msq, header is 1 lines where Date(@3)>=today()-7; STORE Heat_meter_qvd into "C:\Heat_meter.qvd" (qvd); Drop Table Heat_meter_qvd;
From that QVD I am importing a table and calculating the peek from the previous date (Consumption)
FYI - It has been working since I made it a couple of days ago, and now it just stopped and have 0 consumption.
Heat_meter_temp_1: LOAD Cme_no, num(Meter_no,'00000000') as Meter_no, Created, Energy_wH, Volume_m3, Volume_flow_m3H, Flow_temp_C, Return_temp_C, Temp_diff_C FROM [C:\Heat_meter.qvd] (qvd); Heat_meter_temp_2: NoConcatenate LOAD *, peek('Energy_wH') as Energy_wH_peek, num(((Energy_wH-peek('Energy_wH'))),'#####,00') as Energy_wH_consumption, peek('Volume_m3') as Volume_m3_peek, num(((Volume_m3-peek('Volume_m3'))),'#####,00') as Volume_m3_consumption Resident Heat_meter_temp_1 order by Meter_no, Created; Drop Field Energy_wH_peek, Volume_m3_peek; Drop Table Heat_meter_temp_1; Heat_meter: NoConcatenate Load * Resident Heat_meter_temp_2 where Energy_wH_consumption>=0 and Volume_m3_consumption>=0; Drop Table Heat_meter_temp_2;
My result is this.
Cme_no | Meter_no | Created | Energy_wH | wH_consump | Volume_m3 | m3_consump |
12055218 | 68727669 | 21-03-2019 00:00 | 41653 | 0 | 1389,65 | 0 |
12055218 | 68727669 | 21-03-2019 00:30 | 41654 | 1389,67 | ||
12055218 | 68727669 | 21-03-2019 01:00 | 41654 | 0 | 1389,69 | |
12055218 | 68727669 | 21-03-2019 01:30 | 41654 | 0 | 1389,71 | |
12055218 | 68727669 | 21-03-2019 02:00 | 41655 | 1389,73 | ||
12055218 | 68727669 | 21-03-2019 02:30 | 41655 | 0 | 1389,76 | |
12055218 | 68727669 | 21-03-2019 03:00 | 41656 | 1389,78 | ||
12055218 | 68727669 | 21-03-2019 03:30 | 41656 | 0 | 1389,8 | |
12055218 | 68727669 | 21-03-2019 04:00 | 41657 | 1389,82 | ||
12055218 | 68727669 | 21-03-2019 04:30 | 41657 | 0 | 1389,84 | |
12055218 | 68727669 | 21-03-2019 05:00 | 41657 | 0 | 1389,86 | |
12055218 | 68727669 | 21-03-2019 05:30 | 41658 | 1389,88 | ||
12055218 | 68727669 | 21-03-2019 06:00 | 41658 | 0 | 1389,91 | |
12055218 | 68727669 | 21-03-2019 06:30 | 41659 | 1389,93 | ||
12055218 | 68727669 | 21-03-2019 07:00 | 41659 | 0 | 1389,95 | |
12055218 | 68727669 | 21-03-2019 07:30 | 41660 | 1389,98 | ||
12055218 | 68727669 | 21-03-2019 08:00 | 41660 | 0 | 1389,99 | |
12055218 | 68727669 | 21-03-2019 08:30 | 41662 | 1390,04 | ||
12055218 | 68727669 | 21-03-2019 09:00 | 41663 | 1390,06 | ||
12055218 | 68727669 | 21-03-2019 09:30 | 41663 | 0 | 1390,08 | |
12055218 | 68727669 | 21-03-2019 10:00 | 41663 | 0 | 1390,1 |
Can you check if the attached looks good
You can see that each row has an associated date. This is in fact not a column, but instead a time index for value. As an index, there can be multiple values for one time myprepaidcenter, and values may be spaced evenly or unevenly across times.
So, you see null instead of a number?
It looks like that 0 is the only one it gets right.
When there should be consumption it is null
It is weird that when the value matches the previous value, you see the script to be working, it is only when they don't match... you get a null....
Is there an if statement in your script when you create the new wH_consump field? Like If(Meter_no = Previous(Meter_no)... or something else? The reason I ask this is because I don't see any issue with your script unless there are things which you have no shared here....
Yes very strange.
I am not using any if statement in the table or load script.
More strange is that I just made a new table in the same app without changes anything and it works fine.
But trying to use the consumption in a bar-chart is still only shows nullvalues.
I wouldn't know what is going on unless I see it and able to run it. Do you think it is possible to share a sample?
No problem, just need to remove some personal data from it.
But this should be enough for you to work with ( i hope) 🙂
Thx Sunny
How is the CSV data corresponds to the data in your app? I was looking at HeatMeter folder and I am not sure what field corresponds to what?
Sorry for that Sunny
Was not sure how much you needed.
Here is the csv data.