Choosing axis for Visualized

[vedant_dulori]

Hi

After going through all documentations that are available to understand Visualizer, for instance https://github.com/hpcc-systems/Visualizer, I am facing difficulties implementing or understanding following things:

- I have come to understanding that when visualizing a dataset, the visualizer chooses its x-axis and y-axis by using the first two data variables in the data set hence, I wanted to know how to let it choose which data variable should y-axis and x-axis represent?
- Also, can you help understand how to implement various function like mapping and filteredBy while declaring the visualizer. For instance, in the following code:
Visualizer.MultiD.area('myChart', /*datasource*/, 'Sales', mappings, /*filteredBy*/, /*dermatologyProperties*/ );
- It would be great if you can provide me links to resources for understanding Visualizer even better

Thank you,
Vedant Dulori

[bforeman]

Hi Vedant!

I have come to understanding that when visualizing a dataset, the visualizer chooses its x-axis and y-axis by using the first two data variables in the data set hence, I wanted to know how to let it choose which data variable should y-axis and x-axis represent?

There are two great examples that show how to control your graph mapping. Go to the Demos folder, and look at areaChart-mappings.ecl and areaChart-mappings-properties.ecl. That Demos folder is also packed with some other great examples.

If there are other funtion parameters that are not clear, you can always open an issue at https://github.com/hpcc-systems/Visualizer/issues.

There is also a new ECL Online lesson that discusses the Visualization support in ECL. Go to the Introduction to ECL (Part 2) Course and check out Lesson 6B.

Regards,

Bob

[gsmith]

In case you havn't seen it there is a brief tutorial here: https://github.com/hpcc-systems/Visuali ... r/tutorial

Step 4 has an example of using the "mappings" to cherry pick certain columns of data for the visualization - the same will work with a regular chart (as long as you keep the first column as the X axis, the rest will be Y axises): https://github.com/hpcc-systems/Visuali ... Step04.ecl

Step 5 has an example of the "filteredBy" option, in this case used to generate an interactive dashboard: https://github.com/hpcc-systems/Visuali ... Step05.ecl

[vedant_dulori]

Thank you so much for you reply however I am still unable to get it working. The problem in my case is that I am creating a dataset with lineno, date (past 20 years), location and price of 3 different crops (wheat, soy and corn) and when I am creating chart using the standard method it gives me line no. vs date graph. However, I think I am not able to use mappings properly where I want month (jan-dec) vs price (of 3 different lines/gradient each representing different crop). Following is my code:

Code: Select all

import Std;
IMPORT Visualizer;

LocalRecord := RECORD
   STRING10 commodity;
    Std.Date.Date_t date;
    STRING25 location;
    DECIMAL8_2 price;
END;
// for CSV file
Local_CSV_Record := RECORD
    STRING lineno;
    STRING date;
    STRING location;
    DECIMAL8_2 price;
END;

// soy
fname := '~::soy_local.csv';


ds := DATASET(fname, Local_CSV_Record, CSV(HEADING(1)));

LocalRecord tolocalrecord(Local_CSV_Record l) := TRANSFORM
  date := Std.Date.FromStringToDate(l.date, '%Y-%m-%d');
  self.commodity := 'Soy';
  SELF.date := date;
  SELF.location := l.location;
  self.price := l.price;
END;

// wheat
fname2 := '~rawcsv::crop::wheat_updated.csv';

ds2 := DATASET(fname2, Local_CSV_Record, CSV(HEADING(1)));

LocalRecord tolocalrecord2(Local_CSV_Record l) := TRANSFORM
  date := Std.Date.FromStringToDate(l.date, '%e-%b-%y');
  self.commodity := 'Wheat';
  SELF.date := date;
  SELF.location := l.location;
  self.price := l.price;
END;
// corn
fname3 := '~rawcsv::crop::corn_updated.csv';

ds3 := DATASET(fname3, Local_CSV_Record, CSV(HEADING(1)));

LocalRecord tolocalrecord3(Local_CSV_Record l) := TRANSFORM
  date := Std.Date.FromStringToDate(l.date, '%Y-%m-%d');
  self.commodity := 'Corn';
  SELF.date := date;
  SELF.location := l.location;
  self.price := l.price;
END;

localDataSet := PROJECT(ds, tolocalrecord(LEFT));
localDataSet2 := PROJECT(ds2, tolocalrecord2(LEFT));
localDataSet3 := PROJECT(ds3, tolocalrecord3(LEFT));

// Merges all data corn + wheat + soy
finalDataSet := localDataSet+localDataSet2+localDataSet3;
// Unique commodities
filterData := finalDataSet(Commodity = 'Corn');
filterData2 := finalDataSet(Commodity = 'Soy');
filterData3 := finalDataSet(Commodity = 'Wheat');
data_exams := OUTPUT(finalDataSet, NAMED('MultiD__test'));
mappings :=  DATASET([  {'Location', 'date.month'},
                        {'Price', 'price'},
                        {'Commodity', 'commodity'}], Visualizer.KeyValueDef);
Visualizer.MultiD.area('myChart', /*datasource*/, 'MultiD__test', mappings, /*filteredBy*/, /*dermatologyProperties*/ );



[bforeman]

Hi Vedant,
I see an issue with your Mappings definition:

Code: Select all

mappings :=  DATASET([  {'Location', 'date.month'},
                        {'Price', 'price'},
                        {'Commodity', 'commodity'}], Visualizer.KeyValueDef);
Visualizer.MultiD.area('myChart', /*datasource*/, 'MultiD__test', mappings, /*filteredBy*/, /*dermatologyProperties*/ );

I think "Location" needs to be "Date"

That said, have you tried running your finalDataset into a cross-tab report, and on your x-axis you have date and the y-axis would be price. Your cross-tab would break on date(month) and commodity. The cross-tab TABLE just has three fields, month, price and commodity. Once you have your data in that format it should be easy to visualize.

I have an example in my archives somewhere so I will dig it up and post the code later today.

Regards,

Bob

[bforeman]

Vedant,

Something like this:

Code: Select all

//Add after your finalDataSet definition
MNum := STD.Date.Month(finalDataSet.Date);
MName := CASE(Mnum,1 => 'Jan',2 => 'Feb',3 => 'Mar',4 =>'Apr',5 => 'May',6 => 'Jun',
          7 => 'Jul',8 => 'Aug',9 =>'Sep',10 => 'Oct',11 => 'Nov',12 => 'Dec','Unk');

//  Output "2D" dataset:  "Mname" v "Price"
OUTPUT(TABLE(finalDataSet, {MName, UNSIGNED4 Sum_Price := SUM(GROUP, Price),Commodity}, Commodity, FEW), NAMED('CommodityPrice_Month'));

//  Create the visualization, giving it a uniqueID "bubble" and supplying the result name "CommPrice_Viz"
Visualizer.MultiD.column('CommPrice_Viz', /*datasource*/, 'CommodityPrice_Month', /*mappings*/, /*filteredBy*/, /*dermatologyProperties*/ );


Bob

[vedant_dulori]

Hi Bob,

Thank you for the help; however, when I tried to implement the above code however I am getting the following graph in the resource section. Also, I am trying to graph months vs average prices as prices are recorded from past 20 years. For reference, the second graph is similar to what I am trying to do.

[bforeman]

Vedant,

If you want average prices than change the SUM function to use AVE.

What does your data output look like and also do you see any graphs on the Visualize tab next to the OUTPUT?

Also what version of the IDE and target cluster are you using?

Bob

[bforeman]

Vedant,

Well, I worked a few values around and created some test data and I have a working graph in the code example below. Is this close to what you are trying to do?

Bob

Code: Select all

import Std;
IMPORT Visualizer;

LocalRecord := RECORD
   STRING10 commodity;
   UNSIGNED4 Date; //Std.Date.Date_t date;
   STRING25 location;
   DECIMAL8_2 price;
END;
               
finalDataSet := DATASET([{'Corn',20200110,'New York',57.50},
                          {'Soy',20191112,'London',27.30},
                                 {'Soy',20191115,'Paris',37.30},
                                  {'Soy',20191116,'Paris',47.30},
                          {'Wheat',20200208,'Paris',22}, 
                          {'Soy',20191010,'Munich',27.50},
                          {'Wheat',20200305,'New York',39.70}],LocalRecord);

MNum := STD.Date.Month(finalDataSet.Date);
MName := CASE(Mnum,1 => 'Jan',2 => 'Feb',3 => 'Mar',4 =>'Apr',5 => 'May',6 => 'Jun',
                   7 => 'Jul',8 => 'Aug',9 =>'Sep',10 => 'Oct',11 => 'Nov',12 => 'Dec','Unk');
mappings :=  DATASET([  {'Month', 'MName'},
                        {'Commodity','Commodity'},
                        {'Price', 'Ave_Price'}], Visualizer.KeyValueDef);
                        // {'Commodity', 'commodity'}], Visualizer.KeyValueDef);
                                    
OUTPUT(finalDataSet,NAMED('OriginalData'));
OUTPUT(TABLE(finalDataSet, {Commodity, MName,DECIMAL8_2 Ave_Price := Ave(GROUP, Price)},
                          Commodity,MName, FEW),NAMED('CrossTabData'));

// Visualizer.MultiD.area('CommPrice_Viz', /*datasource*/, 'CommodityPrice_Month', /*mappings*/, /*filteredBy*/, /*dermatologyProperties*/ );
Visualizer.MultiD.column('CommPrice_Viz', /*datasource*/, 'CrossTabData', mappings, /*filteredBy*/, /*dermatologyProperties*/ );