Tag: columns

Pulling info from one of several columns

When documents are added to my library, one of 4 approval workflows runs based on the metadata entered on the item (if the document is for Sales, the Sales approval workflow runs, etc). I would like have one column that displays the status of the approval workflow. Since currently, the status of the workflow would appear in one of 4 columns (for whichever workflow is running) – i would like to condense this information into 1 column for simplicity. Is there a way to write a formula (or any other method) that would display the status of the workflow in 1 column for all items?

Efficiently finding the unique non-zero columns after a variable column in a data table

Suppose I have the following data:

        tempmat=matrix(c(1,1,0,4,1,0,0,4,0,1,0,4, 0,1,1,4, 0,1,0,5),5,4,byrow=T)         tempmat=rbind(rep(0,4),tempmat)         tempmat=data.table(tempmat)         names(tempmat)=paste0('prod1vint',1:4)         tempmat[,firstnonzero:=c(NA,1,1,2,2,2)]

So the data table looks like this:

> tempmat    prod1vint1 prod1vint2 prod1vint3 prod1vint4 firstnonzero 1:          0          0          0          0           NA 2:          1          1          0          4            1 3:          1          0          0          4            1 4:          0          1          0          4            2 5:          0          1          1          4            2 6:          0          1          0          5            2

I want to find the number of nonzero elements to the RIGHT of the column indicated by “firstnonzero”.

To make this clear, the desired output then is:

> tempmat    prod1vint1 prod1vint2 prod1vint3 prod1vint4 firstnonzero numbernonzero 1:          0          0          0          0           NA            NA 2:          1          1          0          4            1             2 3:          1          0          0          4            1             1 4:          0          1          0          4            2             1 5:          0          1          1          4            2             2 6:          0          1          0          5            2             1

This is because, for example, on row 2, there is a nonzero element in prod1vint2 and prod1vint4, so the number of nonzero elements to the right of the first nonzero element is 2, and so forth.

This needs to be efficient and scale well, so it cannot be an apply or looping style solution.

Magento 2 create admin config table with custom rows and columns

I want to create admin config with a table to take the data and save in config for that I followed this link.

But I also want to create custom rows in that table programmatically and also without the last action column and add button. Please refer the image Admin Config

I was unable to find any solution on the web regarding the same nor the file 

\Magento\Config\Block\System\Config\Form\Field\FieldArray\AbstractFieldArray

that we extend gave any clues.
Could have easily done this via jquery but want to implement the standard solution.

Code:

<?php namespace Abc\Paymentmethod\Block\Adminhtml\System\Config\Form\Field;  class Feetable extends \Magento\Config\Block\System\Config\Form\Field\FieldArray\AbstractFieldArray {     /**      * @var \Magento\Framework\Data\Form\Element\Factory      */     protected $  _elementFactory;      /**      * @param \Magento\Backend\Block\Template\Context $  context      * @param \Magento\Framework\Data\Form\Element\Factory $  elementFactory      * @param array $  data      */     public function __construct(         \Magento\Backend\Block\Template\Context $  context,         \Magento\Framework\Data\Form\Element\Factory $  elementFactory,         array $  data = []     )     {         $  this->_elementFactory  = $  elementFactory;         parent::__construct($  context,$  data);     }     protected function _construct(){         $  this->addColumn('noi', ['label' => __('NOI'),'readonly'=>'readonly']);         $  this->addColumn('fixed', ['label' => __('Fixed')]);         $  this->addColumn('percent', ['label' => __('Percent')]);         $  this->_addAfter = false;         $  this->_addButtonLabel = __('Add More');         parent::_construct();     }      protected function _prepareArrayRow(\Magento\Framework\DataObject $  row) {         $  options = [1,2,3];         $  row->setData('option_extra_attrs', $  options);     }  }

system.xml

<field id="abc_fee_table" translate="label comment tooltip" sortOrder="17.4" showInDefault="1" showInWebsite="1" showInStore="0">                     <label>Abc Fee by Number of Installments(NOI)</label>                     <frontend_model>Abc\Paymentmethod\Block\Adminhtml\System\Config\Form\Field\Feetable</frontend_model>                     <backend_model>Magento\Config\Model\Config\Backend\Serialized\ArraySerialized</backend_model>                                     </field>

Mysql query to get all columns and its sub contents from other table by its id

I have two tables named “tasks” and “comments”.

I want to get all tasks from “tasks” table.

“comments” table has 2 columns: description and task_id.

task_id is foreign key from task table.

$  tasks = DB::table('tasks')        ->join('comments','tasks.id', '=','comments.task_id')        ->select('tasks.*','comments.* as comments')        ->get();

This code returns error.

How can I do this?

How can I get columns details of a mysql table with select statment and insert into another table for further processing?

I am new to mysql and using mysql with CLI. I have a requirement to get column details with select statement and insert obtained result into another table for further processing.

The requirement is about to retrieve information about table and its columns. And have to ingest these details in another_table.

Table_Name | Column_Name | Column_Type | Column_Size | Is_Primary_Index_Column | Is_Foriegn_Key_Column   select * from tablename_columns  insert into another_table

Thanks and appreciate your help.

Aggregate Pandas Columns on Geospacial Distance

I have a dataframe that has 3 columns, Latitude, Longitude and Median_Income. I need to get the average median income for all points within x km of the original point into a 4th column. I need to do this for each observation.

I have tried making 3 functions which I use apply to attempt to do this quickly. However, the dataframes take forever to process (hours). I haven’t seen an error yet, so it appears to be working okay.

The Haversine formula, I found on here. I am using it to calculate the distance between 2 points using lat/lon.

from math import radians, cos, sin, asin, sqrt  def haversine(lon1, lat1, lon2, lat2):      #Calculate the great circle distance between two points      #on the earth (specified in decimal degrees)      # convert decimal degrees to radians      lon1, lat1, lon2, lat2 = map(radians, [lon1, lat1, lon2, lat2])      # haversine formula      dlon = lon2 - lon1      dlat = lat2 - lat1      a = sin(dlat/2)**2 + cos(lat1) * cos(lat2) * sin(dlon/2)**2     c = 2 * asin(sqrt(a))      r = 6371 # Radius of earth in kilometers. Use 3956 for miles     return c * r

My hav_checker function will check the distance of the current row against all other rows returning a dataframe with the haversine distance in a column.

def hav_checker(row, lon, lat):      hav = haversine(row['longitude'], row['latitude'], lon, lat)      return hav

My value grabber fucntion uses the frame returned by hav_checker to return the mean value from my target column (median_income).

For reference, I am using the California housing dataset to build this out.

def value_grabber(row, frame, threshold, target_col):      frame = frame.copy()      frame['hav'] = frame.apply(hav_checker, lon = row['longitude'], lat = row['latitude'], axis=1)      mean_tar = frame.loc[frame.loc[:,'hav'] <= threshold, target_col].mean()      return mean_tar

I am trying to return these 3 columns to my original dataframe for a feature engineering project within a larger class project.

df['MedianIncomeWithin3KM'] = df.apply(value_grabber, frame=df, threshold=3, target_col='median_income', axis=1)  df['MedianIncomeWithin1KM'] = df.apply(value_grabber, frame=df, threshold=1, target_col='median_income', axis=1)  df['MedianIncomeWithinHalfKM'] = df.apply(value_grabber, frame=df, threshold=.5, target_col='median_income', axis=1)

I have been able to successfully do this with looping but it is extremely time intensive and need a faster solution.

Cascading lookups/filtered choice columns in modern sharepoint

We are using modern sharepoint pages. I currently have a document library with a column that indicates the subject of the document. there’s a 2nd column that has the sub subjects. I’m looking to set up what I believe is called a cascading look-up….but I’ve had trouble finding how to do that in modernSP… Where should i be looking for this?

sql server show multiple columns one by one using pivot

sql server i need

record as 

    SELECT * from ( Select  sum(StockPosting_Qty) as StockPosting_Qty ,Batch_Id,Batch_No,convert(varchar,Batch_PackedDate,105)as PackedDate ,convert(varchar,Batch_ExpiryDate,105)as ExpiryDate,IT.Item_Name,ID.Item_ConversionUnit,Branch_Name,0 amount,Batch_SellingPrice from tbl_Batch left outer join tbl_StockPosting SP on Batch_Id=StockPosting_BatchID  inner join tbl_Item IT on IT.Item_Id=Batch_ItemId inner join tbl_ItemDetail ID on IT.Item_Id=ID.ItemDt_ItemId left outer join tbl_Branch BH on BH.Branch_ID=SP.StockPosting_BranchId  where StockPosting_Date between '2019-03-01' and '2019-05-05'  and StockPosting_CompanyId='1' and StockPosting_BookType=case when '1'<>0 then '1' else StockPosting_BookType end Group by Batch_Id,Batch_No,Batch_SellingPrice,Branch_Name,Batch_PackedDate,Item_Name,Batch_ExpiryDate,Item_ConversionUnit   ) up PIVOT (Sum(StockPosting_Qty) for Branch_Name in ([KALYAN], [MUMBAI], [NERUL], [RETAILSOFT], [RETAILSOFT MUMBAI], [RETAILSOFT TECHNOLOGIES], [STORE], [THAEN], [THANE], [TURBHE], [VASHI], [WAREHOUSE],[KALYAN Amount], [MUMBAI Amount], [NERUL Amount], [RETAILSOFT Amount], [RETAILSOFT MUMBAI Amount], [RETAILSOFT TECHNOLOGIES Amount], [STORE Amount], [THAEN Amount], [THANE Amount], [TURBHE Amount], [VASHI Amount], [WAREHOUSE Amount])) AS pivo  --PIVOT (Sum(StockPosting_Qty) FOR amount  IN ([KALYAN Amount], [MUMBAI Amount], [NERUL Amount], [RETAILSOFT Amount], [RETAILSOFT MUMBAI Amount], [RETAILSOFT TECHNOLOGIES Amount], [STORE Amount], [THAEN Amount], [THANE Amount], [TURBHE Amount], [VASHI Amount], [WAREHOUSE Amount])) AS P2 order by Batch_Id asc [KALYAN Amount], [MUMBAI Amount], [NERUL Amount], [RETAILSOFT Amount], [RETAILSOFT MUMBAI Amount], [RETAILSOFT TECHNOLOGIES Amount], [STORE Amount], [THAEN Amount], [THANE Amount], [TURBHE Amount], [VASHI Amount], [WAREHOUSE Amount]