pentaho , Merge fields in pentaho

Merge fields in pentaho


Tag: pentaho

I have two columns "ID_TT" in select values 1 and "ID_ARC" in select values 2.

ID_TT has below values




ID_ARC has below values




I need to merge these two . I used calculator but it does not work. How can we solve this.

output must be




enter image description here


Here is the solution. I think its useful to you

enter image description here

If you follow above step you can get that result like these.


enter image description here

Thank you.


Calculated columns in pentaho Cde

I am new to the use of Pentaho and let me know how it works the "Calculated Columns" option into "sql query" object. I need to calculate the average value.

Pentaho User Console(PUC) parameter error

When I publish the prpt file in Pentaho User Console(PUC), I am getting error in parameter field when I select parameter values. after select the value from drop down the value is disappear. (I have attached image) .I have selected those values in prpt(Check the image) but its working fine...

How to change second y-axis text font size in pentaho ccc charts

How to change second y-axis text font-size in pentaho chart I putted some text (i.e Monthly Cost ($000) in orthoAxisTitle it shows fine. How to put some text in second y-axis also...

Pentaho Data Integration User Defined Java Class

I create simple java class and export it to jar: package test; public class Test { public Test() { // TODO Auto-generated constructor stub } } Jar file add to lib folder in Pentaho (there are many jar files) Next step I want to use my class in Pentaho Data...

Redirect to the browse files screen pentaho when logging in

I want to set browse files page as home page for Pentaho BI server. By default we need to click on Browse files button to see our files. I want see my files in my home page Can anybody please help me ?...

Table output name from command line in pentaho kettle

There is a case in my ETL where i am trying to take "table output" name from command line. The table name does not correspond to any streaming field's name. Is there any way to get it done in pentaho kettle?

Issue with create dimension table with last update time column in Pentaho Data Integration

I am creating dimension table with last updated time(from GetSystemInfo)in Pentaho Data Integration(PDI).It works fine except it enters new rows even there is no changes in row and reason is there is lookup is also performing on last updated time field which should not perform. But when I removes this...

Merge fields in pentaho

I have two columns "ID_TT" in select values 1 and "ID_ARC" in select values 2. ID_TT has below values [blank] 121 [blank] ID_ARC has below values 146 [blank] 171 I need to merge these two . I used calculator but it does not work. How can we solve this. output...

Import .prpt file in Pentaho Server using Command Line

I want to upload .prpt (Pentaho Report File) in Pentaho BI Server. I am using the following command: ./ --import --url=https://server/pentaho/ --username=user --password=pass --source=file-system --type=files --charset=UTF-8 --path=/public--file-path=/home/kishan/folder/Clients/abc/Daily_Reports/Prpt/xyz.prpt --logfile=/home/user/upload.log --permission=true --overwrite=true --retainOwnership=true So, I want to pick up the file located at the file-path value above and upload it to the...

Generate a random value in the set {0,1}

Actually the Generate random value input allows me to generate an random int, but not in the set I want. How to generate a random value in the set {0,1} with Pentaho Data Integration ?...

Pentaho to convert tree structure data

I have a stream of data from a CSV. It is a flat structured database. E.g.: a,b,c,d a,b,c,e a,b,f This essentially transforms into: Node id,Nodename,parent id,level 100, a , 0 , 1 200, b , 100 , 2 300, c , 200 , 3 400, d , 300 , 4...

How to set field in previous step as JASON Output File Name in Pentaho?

I want to use a Concatenated field as the Json output filename in my Pentaho Data Integration transformation, but as long as I don't see any "Accept field as filename" option, I don't know how to make this happen. Could someone help me to sort it out? Thanks in advance!...

Split the text file on date based in pentaho

I have one text file and I want to split this file into multiple output files on the basis of dates. Dates Keywords 201506-17 iphone 5 201506-16 iphone 4 201506-15 iphone 3 201506-14 iphone 2 201506-13 iphone 1 ...

Checkpoints in Pentaho Spoon

The pentaho documentation ( specifies that, as of version 5.0, you can define "checkpoints" and "checkpoint logs" to let you restart ETL jobs from the most recently failed point so you don't have to go back and re-run a bunch of steps that already completed successfully. I'm running Pentaho Data...

'Too many connections' created in postgres when creating a dashboard in Pentaho

I was creating a Dashboard in Pentaho PUC which uses a postgres connection as the data source. Most of the time this causes the postgres to say Too many clients already in Postgres' SHOW max_connections; Query shows maximum connections of 200 I used this query select * from pg_stat_activity;. From...

How to change display value in parameter selection pentaho reports

I need to display "All" when my data in parameter list is -1. Just to display in the parameter selection. Help me with this Thanks, Keerthi KS...

hide a sub report containing a chart pentaho

Hi we are using Pentaho report designer and we want to hide a subreport if there is no data . We have tried to use this formula : not(isemptydata()) in the visible expression but it does not seem to work . So how to hide a subreport if no data...

pentaho report designer display field over field2 if 'field2' is not present

Let's say I want to make 2 text fields next to eachother. On the screenshot below you can see 2 fields. The field on the left will always show. But if the field on the right isn't filled in, I want to make it disappear but the text from the...

Pentaho report designer , checkbox parameter checked by default

we have set a check box parameter in Pentaho report designer . When we launch the report the check box is unchecked . what we want is , to set the default value of that Check box to be checked so when we launch the report we don't have to...

Pentaho Dimension lookup/update

I have seen Dimension Lookup/Update documentation here and a few other blogs. But I cannot seem to get a clear idea. I have a table with the following structure: Key Name Code Status IN Out Active The key name code status active comes from a csv file . I need...

Hierarchies and levels (Pentaho schema workbench)?

I'm new in BI world and I have a lot of questions. I have to do a BI home work project, so I decided to use: MYSQL (database) Pentaho Kettle (ETL) Pentaho schema workbench (star schema) QlikView (reporting) I have a dimension table which is SUPERMARKET and it's edited from...

Loading fact table with SCD type 2 dimension

I have got a dimension tables with 1 million records which is SCD type 2.I am using pentaho Dimension lookup step for populating this dimension table. I am getting a version number,start date and end date. Now I want to populate the fact table based on the scd type2. What...

Generate pentaho report designer prpt file using java

My question is, how can I generate Pentaho Report Designer's saved .prpt file using java, without Report Designer itself? Is there any libraries for that? I need to generate those files programmatically, then later open using Report Designer and fix some values....

Pentaho convert string to integer with decimal point

I am importing text values into a transformation using a Fixed Width input step. Everything is coming in as a string. I want to convert some of the string values to integers with a decimal point at a specified spot. Here are some examples of the before (left hand side)...

How to Reload CDA and Mondrian cache in Pentaho CE 4.8?

I'm currently stuck in some performance issue for my Dashboard. I've created a dashboard in Pentaho Community edition 4.8. For my charts, using the SQL and MDX (Mondrian) queries. My Problem is that, When I first time open my dashboards after clearing cda and Mondrian cache. It take 50 secs...

In kettle use text file input read csv file from a tar.gz file but it didn't worked. Where it might be wrong?

I have a csv file that is tared and zipped. So I have test.tar.gz. I would like, through text file input, read csv file. I try this tar:gz:file://C:/test/test.tar.gz!/test.tar! use wildcard like ".*\.csv". But it sometime can't read success. It throws Exception org.apache.commons.vfs.FileNotFolderException: Could not list the contents of "tar:gz:file:///C:/test/test.tar.gz!/test.tar!/" because...

Pentaho - CSV Input not understanding special character [Windows to Linux]

I have a transformation on Pentaho Data Integration where the first thing I do is I use the "CSV Input" to map my flat file. I've never had a problem with it on windows, but now I'm chaning my server that spoon is going to run to a linux server...

Pentaho Kettle: how to pass variable from transformation to another transformation inside job

I have two transformations in the job. In the first trasnformation - I get details about the file. Now I would like to pass this information to the second transformation, I have set variable in the settings parameters of the trasnformation #2 and use Get Variables inside - but the...

How to create an embedded document from a table using pentaho

I have to table student and record, the relationship is a student have many records (one to many). How I can represent a transformation on pentaho so that I can insert every line in the record table as an embedded document in the student document. All this is for migrate...

Pentaho CDE nested sql query

We have set a nested SQL query on pentaho CDE . Query : select dataissue.value,count(value) as nbreticket,substring(issue.entry,1,3) from DataIssue,issue where field = 'version(s)_corrigée(s)' and dataissue.issue = and issue in ( select issue from dataissue,issue where dataissue.issue = and value = 'récit' and substring(issue.entry,1,3) = 'ema' ) and issue...

Generating a dynamic date based on a row number using pentaho pdi

I want to generate a date dynamically based on row numbers using pentaho pdi. for example: row 1 =====>Date=2015-06-08 **01**:56:30 row 2 =====>Date=2015-06-08 **02**:56:30 row 3 =====>Date=2015-06-08 **03**:56:30 row 4 =====>Date=2015-06-08 **04**:56:30 All my data come from an excel spreadsheet with row number and date fields and I want the...

How to return no matched row in Pentaho Data Inegration (Kettle)?

I look for a solution to perform SSIS lookup in Pentaho Data Integration. I'll try to explain with an exemple : I have two tables A and B. Here , data in table A : 1 2 3 4 5 Here , data in table B: 3 4 5 6...

Saiku File not showing in Ivy Dashboard Designer Pentaho BI Server CE

Hi I am using pentaho Bi Server community edition. I created a Saiku Analytics File (say demo.saiku) and saved it in /home/admin folder. After that i created a new Ivy Dashboard, Drag and dropped an Analytics Menu in a dashboard Window. Set the title and layout properties. Now when i...

Pentaho Kettle Error in Archive Files - org.apache.commons.vfs.FileSystemException: File closed

I have a job which is set to archive files in a directory. It looks like it is running into the error org.apache.commons.vfs.FileSystemException: File closed when it attempts to create the zip file. However, the zip file does get created, and the files are added to it. I've sent the...

Simple MYSQL count, group by, not working using Pentaho Report Designer CE

I need to write a query which will pull from two different tables, count the results and return to me in one row, the total results. I've come across a few problems. When I run the query without a count expression, I am returned 645 rows. 645 is the correct...

Merge 2 facts in cube?

Is it possible to merge 2 facts tables to create a cube in a Mondrian schema example the case of sales and cost ?

Kettle - Read CSV with comma as decimal mark

I have a transformation on Pentaho Data Integration (aka Kettle) where the first thing I do is I use the "CSV Input" to map my flat file. I've never had a problem with this step on windows, but now I'm chaning the server where spoon is going to run to...

Increment the year by 1 if month is December

I passed the year and month separately as parameters in pentaho and add month by 1 and convert to date format. I wrote like this to join year, month and date. ('${year}' || '-' || '${month}'+1 || '-' || 1 )::date I need to increase the month by 1 from...

Implementing SCD Type 2 using Pentaho Kettle (Pentaho Data Integeration 5.2)

I am having a table, plan, with columns p_id,p_name,start_date,end_date,last_updated Problem Statement: when a customer changes from plan A to plan B, its end_date corresponding to plan A gets updated in the table and at the same time a new record for plan B inserted into the table. I am creating...

Execute .jar file in Spoon (Pentaho Kettle)

I need to execute a java jar file from Spoon. The program has only one class, and all I want is to run it with or without parameters. The class is named "Limpieza", and is inside a package named: com.overflow.csv.clean I have deploy the jar to: C:\Program Files (x86)\Kettle\data-integration\lib And...

Pentaho: Insert a set of dynamic records into a database

Using Pentaho, I would like to SELECT a number of records from a database and INSERT them into another one. I have no problem with the first part and using Input Table step, I have selected my desired records. But I have no idea about how to develop a step...

Loop over file names in sub job (Kettle job)

The task is to get file names from the folder and then loop the same task (job) over all the files one by one. I created a simple job with transformation (get files names) and then job with flag "Execute for each row" (now is just logging the name of...

Using the (A*B) function in calculator- Pentaho spoon-

I'm trying since yesterday to use the function (A * B), very simple like operation, but it does not work. Any help! thank you.

Comparing filenames in PDI

I am trying to import a certain .CSV file into my database using PDI (Kettle). Normally this would be rather easy, as you could just link up a CSV file input step with a Table output step and be good to go. However, the problem is that I don't know...

Dummy step is not work in Job

Each transformation will create an csv file in a folder, and I want to upload all of them when transformations done. I add a Dummy but the process didn't work as my expectation. Each transformation will execute Hadoop Copy Files step. Why? And how could I design the flow? Thanks....