Databases
StackOverflow2013
Postsdbo
POGet difference to last month-end
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGet difference to last month-end
    primarykey
    data
    text
    <p>Per each day (TRADEDATE) I have an entry which is uniquely identified by BOOK and COMMODITY. Each entry has a Present Value (PV) which changes daily. I would like to get a column with the difference of the PV to the last calendar day of the previous month. I solved it by means of a loop but I was wondering if anybody can suggest a more elegant (and faster solution):</p> <pre><code>library(data.table) bwTab COMMODITY BOOK TRADEDATE PV Desired Column 1: CASH HS_OPT_GEN 2012-09-30 66669.68 NA 2: CASH HS_OPT_GEN 2012-10-01 76333.83 9664.15 3: CASH HS_OPT_GEN 2012-10-02 76333.83 9664.15 4: CASH HS_OPT_GEN 2012-10-03 76333.83 9664.15 5: CASH HS_OPT_GEN 2012-10-04 76333.83 9664.15 --- 3050: OIL HO_OIL_FIN 2012-09-30 21330.55 NA --- 3066: OIL HO_OIL_FIN 2012-10-26 42661.28 21330.73 3067: OIL HO_OIL_FIN 2012-10-27 21330.69 0.14 3068: OIL HO_OIL_FIN 2012-10-28 21330.68 0.13 3069: OIL HO_OIL_FIN 2012-10-29 21330.78 0.23 # Here is my solution # Define a function for last day of previous month pme &lt;- function(date) {as.Date(paste("01",month(date),year(date),sep="."),"%d.%m.%Y")-1} difftopme &lt;- function(a) { if (nrow(bwTab[COMMODITY==a[,COMMODITY] &amp; BOOK==a[,BOOK] &amp; TRADEDATE==pme(a[,STICHTAG]),])==0) {NA} else { a[,PV]-bwTab[COMMODITY==a[,COMMODITY] &amp; BOOK==a[,BOOK] &amp; TRADEDATE==pme(a[,TRADEDATE]),PV] } } for (i in 1:nrow(bwTab)){a &lt;- difftopme(bwTab[i,]) ; if (i==1){diffPVme &lt;- a} else {diffPVme &lt;- c(a,diffPVme)}} ######################### dput(bwTab[1000:1010,]) structure(list(COMMODITY = c("ELEC", "ELEC", "ELEC", "ELEC", "ELEC", "ELEC", "ELEC", "ELEC", "ELEC", "ELEC", "ELEC"), BOOK = c("HS_OUK_MKT", "HS_OUK_MKT", "HS_OUK_MKT", "HS_OUK_MKT", "HS_OUV_EVO", "HS_OUV_EVO", "HS_OUV_EVO", "HS_OUV_EVO", "HS_OUV_EVO", "HS_OUV_EVO", "HS_OUV_EVO" ), STICHTAG = structure(c(1353798000, 1353970800, 1354057200, 1354143600, 1348956000, 1349042400, 1349128800, 1349215200, 1349301600, 1349388000, 1349474400), class = c("POSIXct", "POSIXt"), tzone = ""), BROKERAGE = c(123406.66, 61791.17, 62229.17, 62492.57, 0, 0, 0, 0, 0, 0, 0), DV = c(72873524.86, 38096138.75, 38283589.07, 38236199.05, 23171721.81, 23178889.59, 23187553.93, 23187426.98, 23173154.67, 23149439.13, 23149469.88), REALIZED = c(47002372.1, 23501186.05, 23501186.05, 23501186.05, 22961528, 22961528, 22961528, 22961528, 22961528, 22961528, 22961528), PV = c(25871152.76, 14594952.7, 14782403.02, 14735013, 210193.81, 217361.59, 226025.93, 225898.98, 211626.67, 187911.13, 187941.88), PV_ND = c(25973196.64, 14654807.46, 14843080.44, 14795220.35, 210222.01, 217386.44, 226048.76, 225920.76, 211641.41, 187919.95, 187949.85), BROKER_R = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), CREDIT_R = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), STRUCTURE_R = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), BROKER_UR_D = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ), CREDIT_UR_D = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), STRUCTURE_UR_D = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), BROKER_UN_UND = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), CREDIT_UN_UND = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), STRUCTURE_UN_UND = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)), .Names = c("COMMODITY", "BOOK", "STICHTAG", "BROKERAGE", "DV", "REALIZED", "PV", "PV_ND", "BROKER_R", "CREDIT_R", "STRUCTURE_R", "BROKER_UR_D", "CREDIT_UR_D", "STRUCTURE_UR_D", "BROKER_UN_UND", "CREDIT_UN_UND", "STRUCTURE_UN_UND"), sorted = c("COMMODITY", "BOOK", "STICHTAG"), class = c("data.table", "data.frame"), row.names = c(NA, -11L), .internal.selfref = &lt;pointer: 0x014024a0&gt;) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USddg
    1. USddg
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POGet difference to last month-end
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POGet difference to last month-end
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POGet difference to last month-end
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWTF? You use `data.table` (not really necessary with only 3k rows) and than loop over the rows with a `for` loop? Can you please use `dput` to provide a representive part of the data.table?
      singulars
      1. POGet difference to last month-end
      1. USRoland
    2. COalso please clean up your sloppy code ;) if you are too lazy to do it yourself, [watch this video](http://www.screenr.com/Vap8)
      singulars
      1. POGet difference to last month-end
      1. USAnthony Damico
    3. COHi Roland, I copied the result of the dput above. I am using the data.table mostly bacause I am used to it (I find the syntax very convenient).
      singulars
      1. POGet difference to last month-end
      1. USddg
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload