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 |
So, these look like the same csv files you shared before, right? What I meant was the I see in your csv file there is a field called #serial-number... what does this corresponds to in your main dashboard... because this is what you are loading from the csv
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
Not sure if this is what you need?
Num(@1) as Cme_no, (#serial-number) Num(@2) as Meter_no, (device-identification) date(@3, 'YYYY-MM-DD hh:mm') as Created, (created) num(@8/1000) as Energy_wH, (energy,Wh,inst-value,0,0,0) Num(@9) as Volume_m3, (volume,m3,inst-value,0,0,0) Num(@10) as Volume_flow_m3H, (volume-flow,m3/h,inst- value,0,0,0) Num(@12) as Flow_temp_C, (flow-temp,°C,inst-value,0,0,0) Num(@13) as Return_temp_C, (return-temp,°C,inst-value,0,0,0) num(@12-@13) as Temp_diff_C
Yes, thank you
Can you check if the attached looks good
Hi Sunny
Thank you for your patience, that did the trick, and I also learned a way to validate that the peek() is done on the right fields every time..
Thx a million 🙂
Heat_meter_temp_2: NoConcatenate LOAD *, peek('Energy_wH') as Energy_wH_peek, If(Cme_no = Previous(Cme_no) and Meter_no = Previous(Meter_no), num(((Energy_wH-peek('Energy_wH'))),'#####,00'), Num(0,'#####,00')) as Energy_wH_consumption, peek('Volume_m3') as Volume_m3_peek, If(Cme_no = Previous(Cme_no) and Meter_no = Previous(Meter_no), num(((Volume_m3-peek('Volume_m3'))),'#####,00'), Num(0,'#####,00')) as Volume_m3_consumption Resident Heat_meter_temp_1 order by Cme_no, Meter_no, Created;
With MyBKExperience research, Burger King wants to inspect customers and get their feedback.
lol that is great... what does that have to do with Qlik?