creating entry points from measurement points

[Yassine_Gh]

Hello ,
I have measurement points with references, name and rate.
I want to create entry points from these points

example:
Ref | name | Rate
   1 | place H | 100
   2 | YY Street | 120

I want to create an entry point that will be 1 + 2 and named 'AAAA' and keep other references
and calculate the average rates for the new entry point.

Thank you.

[miguelbraga]

Hey there,

Please use this code:

LOAD @1 as Ref,
@2 as name,
@3 as Rate
FROM
[C:\Users\Administrator\Desktop\Data.txt]
(txt, codepage is 1252, explicit labels, delimiter is '|', no eof, filters(
Remove(Row, Pos(Top, 1))
));

LOAD
3 as Ref,
'AAAA' as name,
Sum(if(Ref = 1, Rate, 0) + if(Ref = 2, Rate, 0)) as Rate
Resident Data;

You'll get something like this:

 

See the Data.txt  and qv1.qvw in the Example.zip attached file.

Best regards,

MB

[Yassine_Gh]

What if we add a Date Column ? Ref | name |Date | Rate 1 | place H |01/01/2019 | 100 1 | place H |02/01/2019 | 90 1 | place H |03/01/2019 | 10 2 | YY Street |01/01/2019 | 120 2 | YY Street |02/01/2019 | 120 Thank you

[miguelbraga]

Then you I would change my original code to this:

Let vRef = 3;
Let vName = 'AAAA';
Let vDate = '12/12/2019';

LOAD @1 as Ref,
@2 as name,
@3 as Date,
@4 as Rate
FROM
[C:\Users\Administrator\Desktop\Data.txt]
(txt, codepage is 1252, explicit labels, delimiter is '|', no eof, filters(
Remove(Row, Pos(Top, 1))
));

LOAD
'$(vRef)' as Ref,
'$(vName)' as name,
Date('$(vDate)') as Date,
Sum(if(Ref = 1, Rate, 0) + if(Ref = 2, Rate, 0)) as Rate
Resident Data;

Which will give you this result:

See the Example.zip attached.

[Yassine_Gh]

thank-you for your prompt response,
the date 12/12/2019 does not correspond to ref 1 or ref 2,
How to find a date that combines the ref 1 or ref 2 to have good aggregation?
Thank you.

[miguelbraga]

Maybe you need a maximum date to aggregate the two records dates? Like this:

Let vRef = 3;
Let vName = 'AAAA';

LOAD @1 as Ref, 
@2 as name, 
@3 as Date,
@4 as Rate
FROM
[C:\Users\Administrator\Desktop\Data.txt]
(txt, codepage is 1252, explicit labels, delimiter is '|', no eof, filters(
Remove(Row, Pos(Top, 1))
));

TempMaxExpNr:
Load Max(Date) as MaxDate resident Data;

Let vDate = Peek('MaxDate');

Drop Table TempMaxExpNr;

LOAD
'$(vRef)' as Ref,
'$(vName)' as name,
Date('$(vDate)') as Date,
Sum(if(Ref = 1, Rate, 0) + if(Ref = 2, Rate, 0)) as Rate
Resident Data;

The result should be like this:

See the Example.zip in attached file.

Best regards,

MB

[Yassine_Gh]

I think the result is not correct, the expected result is :
3 | AAAA | 01/01/2019 | 220: 100 + 120
3 | AAAA | 01/01/2019 | 210: 90 + 120
(100) of Ref1 as of 01/01/2019 + (120) of Ref2
Thank you

[miguelbraga]

Then it should be something like this:

Let vRef = 3;
Let vName = 'AAAA';

LOAD @1 as Ref,
@2 as name,
@3 as Date,
@4 as Rate
FROM
[C:\Users\Administrator\Desktop\Data.txt]
(txt, codepage is 1252, explicit labels, delimiter is '|', no eof, filters(
Remove(Row, Pos(Top, 1))
));

LOAD
'$(vRef)' as Ref,
'$(vName)' as name,
Date,
Sum(if(Ref = 1, Rate, 0) + if(Ref = 2, Rate, 0) ) as Rate
Resident Data
Group by
Date
;

Look at the final result

Refer to the attached file.

Best Regards,

MB