How to load only the first and last record of a flat file into the target?

kishor Baindoor

• Jan 23rd, 2016

Connect source to two aggregator and then union it. In aggregator, if we donot check any column for aggregation then it will return last record by default. We can change this property in 2nd aggregator to return first record. When we union it we will get first and last record.

Raghav

• Apr 4th, 2015

Using rank and expression transformation.
More details at below link
http://www.raghavatal.com/2015/04/04/how-to-load-only-the-first-and-last-row-from-a-flat-file-to-a-target-file/

Answer Question  Select Best Answer

Performance wise which is better connected or unconnected lookup transformation?

priya

• Jan 13th, 2016

Connected and unconnected lookup depends on scenarios and performance. If you are looking for a single value for look up and the value is like 1 in 1000 then you should go for unconnected lookup. Perf...

Answer Question  Select Best Answer

What is the difference between SQL Override and Lookup Override

VENKATM

• Jan 2nd, 2016

You cannot use directly Union in Lookup Override. You can use in SQL Override. No join conditions in SQL Override. Join condition is compulsory in lookup.

dbseeker

 Profile Answers by dbseeker Questions by dbseeker

• Dec 20th, 2015

Can we have a different opinion on the 4th point? "Lookup override gives only one record even though there..." A lookup can return more than one record. We could handle this using the lookup property ...

Answer Question  Select Best Answer

How and for what purposes we use look up transformation in Sales or Banking Project?

Raju Gupta

• Nov 25th, 2015

Look up is basically used for searching something. In case of banking project, consider a case where you have the customers id in one file and its salary stored in some other file. Now if you want to ...

Answer Question  Select Best Answer

How to find out duplicate records using aggregator. Pls explain with example. What is the need for placing joiner after aggregator. I just tried like the following:Count the records and group by all the columns. if the count=1 then it will be a valid record. what to do if the count >2.means file contains duplicate records.Why we need to use joiner after aggregator?

VISHNU VARDHAN

• Nov 17th, 2015

Use HAVING Condition in SELECT clause, You can write code like below to test, if it is greater than one then it is duplicate otherwise Unique.

Code
 

priyank_divya

 Profile Answers by priyank_divya Questions by priyank_divya

• Aug 7th, 2012

Hi, Its similar to the SQL query, SELECT * FROM ,,.... FROM TABLE_NAME GROUP BY ,,..... HAVING COUNT(*)=1 Similarly in Informatica Aggregator transformation, select group by for all the columns and...

Answer Question  Select Best Answer

Design mapping to load the first record from a flat file into Table A, the Last record from a flat file into Table B, and remaining record into Table C

Nagendra

• Nov 5th, 2015

Append Seq Gen after SQ, add sorter, make it desc based on Sequence generated and use a variable to make first row as 0 then by using router route SEQGEN = 1 to A (First Row), Variable =0 to B (Last Row) and rest to C

sum

• Oct 8th, 2015

Use aggregator to take count of rows; then again join this aggregator to source pipeline using dummy columns. Now add one sequence generator and add next_val as srnum from it to each source columns...

Answer Question  Select Best Answer

How to override LOOKUP in Informatica?
How to tune LOOKUP in informatica?

Niranjan

• Nov 5th, 2015

Use an SQL ovveride in properties. Query should contain only the required columns and required joins based on requirement. Grouping the Columns in the SQL Override yield better results.

Answer Question  Select Best Answer

Can we use more than one parameter file in a workfow? If yes, how? If No, Why?

ishan

• Nov 1st, 2015

Yes, we can. In case of concurrent execution of workflow. Go to workflow, properties, there is a option of concurrent execution. In case we enable it we have an option of running multiple instances of workflow using different parameter files.

Answer Question  Select Best Answer

Suppose you have 2000 records in one table and 12000 in another which one you will consider as master and detail?

Read Best Answer

Editorial / Best Answer

ghola  

 Profile Answers by ghola Questions by ghola

• Member Since Jul-2008 | Aug 15th, 2008

---

The joiner transformation compares each row of the master source against the detail source. Hence, fewer number of rows in master means fewer iterations of join comparison.

Secondly It is easier to cache the table with fewer number of rows.

Hence, using the table having the fewer number of rows as a master improves the performance.

Raju R

• Oct 27th, 2015

@ponnana: The performance of the joiner depends on the rows and not on the columns. Also you take up the condition which consists n number of columns. Based on the condition the rows are obtained. So We need not worry about the columns.

Rashmi

• Oct 18th, 2015

The join is happening based on some condition. Therefore match process depends on the condition columns only and not how many columns are present in total. The table you drag into joiner first is considered as Master.

Improve Answer

Suppose I have one source which is linked into 3 targets.When the workflow runs for the first time only the first target should be populated and the rest two(second and last) should not be populated.When the workflow runs for the second time only the second target should be populated and the rest two(first and last) should not be populated.When the workflow runs for the third time only the third target...

sum

• Oct 8th, 2015

Define one variable $$CNT in mapping and assign its initial value as 1 in parameter file Take an expression and do below coding count =$$CNT INC_COUNT=$$CNT+1 O_INC_CNT=SETVARIABLE($$CNT,INC_COUNT) Ad...

Rakesh

• Feb 9th, 2015

your above logic is perfect..but instead resting the variable at workflow level you can add these condition in first router group => MOD(o_Flag,3) = 1 2nd router group =>MOD(o_Flag,3) = 2 3rd router g...

Answer Question  Select Best Answer

Can any one explain where we use Unix shell Scripts in informatica? Provided with example if possible.Thanks.......Jc

sum

• Oct 8th, 2015

1] To archive file
2] To create parameter file dynamically at run time
3] To have control and audit for source file/tables
4] To purge old files from server

savaram5553

 Profile Answers by savaram5553 Questions by savaram5553

• May 28th, 2015

Hi Nithin will you help me with batch script to bounce the informatica and DAC services.

Answer Question  Select Best Answer

How to generate a star pattern using Informatica depending on the number of rows in the input file

Suppose there are 7 rows in the source file (any type of data), the target should have 7 rows of stars in the following pattern:-

*
***
*****
*******
*********
***********
*************

SUM

• Oct 8th, 2015

V_star=iif( v_vount < = 1,*,rpad(v_star,vcount,*))
v_count=v_count+2
O_STAR=V_STAR

Answer Question  Select Best Answer

sudeep

• Sep 18th, 2015

In general Fact table is normalized and Dimension table is denormalized. So that you will get all required information about the fact by joining the dimension in STAR schema. In some cases where dimensions are bulky then we we snowflake it and make it normailzed.

Niranjan

• Apr 23rd, 2015

Normalization is splitting 1 table to multiple tables to avoid insert/update and delete anomalies. They are done in OLTP systems. De-normalization is exact opposite of normalization, here multiple tab...

Answer Question  Select Best Answer

How to load 10000 records into dynamically created 100 files and to load 100 records in each?

Snehasis

• Sep 12th, 2015

This can be done using TC transformation. In an expression before the TC, keep a counter which increments for each row passed. When the counter reaches 100, reset the counter to 0. In the expression...

Answer Question  Select Best Answer

how to join the data of two tables which do not have common columni mean how to perform non equi join in informaticaEg:just like getting data from emp and salgrade table of oracle where sal is between losal and hisalthanksvenu

milind

• Aug 6th, 2015

Yes dummy column will work
other way is to use mapping variable :)

ashish

• Sep 27th, 2014

You can create a dummy port in each source and assign 1 to both port and drag the both sources in joiner tr and give join condition on dummy port, After that you can use filter condition on your location

Answer Question  Select Best Answer

( cross platform integration) - I am new to informatica n SAP , i got assignment work on integration of informatica and SAP R/3 , i need to import data from SAP , i went to could of videos on YouTube provided by Informatica Support , they are saying you need to install RFC libraries that are available on SAP market place, RFC libraries are platform specific , i do have SAP system on Unix and informatica...

prashanth naik

• Jul 7th, 2015

You can do it by configuring the Application connection, the connection will generate the ABAP code which hits SAP R/3 system and does data extraction.

Answer Question  Select Best Answer

In SCD Type2 scenario, Informatica has no control on the DML operation.
How will we make sure that updates happen before Inserts in SCD Type2 dimension so that we can avoid the AK constraint error.

Abhilash tyagi

 Profile Answers by Abhilash tyagi Questions by Abhilash tyagi

• Jun 6th, 2015

You can try to change the name of the target instances. For example, let us consider we have 2 instances of targets CUST_D (insert) and CUST_D1 (update), try to change the name of insert instance to C...

Answer Question  Select Best Answer

What is Dimensional modelling ? What is the difference between Data modelling and Dimensional modelling and Erwin modelling.

Manjunath

• Apr 9th, 2015

both Dim Modeling and ER modeling can be 3NF, which is highly normalized, As raj mentioned ER is used for OLTP and DM for OLAP. in both the cases data is stored in a set of tables and related to each ...

Sharat

• Jan 5th, 2015

Raj, 1NF is highly denormalized and the higher forms are more normalized. You said Dimensional Modeling is for datawarehouses and uses 3NF. Contains denormalized data. 3NF is highly normalized and how can the datawarehouses can have denormalized data

Answer Question  Select Best Answer

Source:
col2 col1
a 1
b 1
c 1
d 1
p 2
q 2
r 2
x 3

how to do this...
thanks
krishna

Manjunath

• Apr 9th, 2015

Converting rows into columns is de-normalizing, you may have to use aggregate transformation to do so. Note: vice verse is normalizing the data, that can be done using normalizer transformation. U...

Shiva

• Jan 14th, 2015

Pivoting means converting rows to column and vise verse.

Please use normalization transformation to achieve this.

Answer Question  Select Best Answer

Milind Jadhav

• Mar 21st, 2015

Informatica follow input variable n output process.thus you can make some calculations in variable port n copy them to the output port to achieve some purpose mostly used in expression tf

Kameshwara

• Aug 8th, 2013

variable ports are used in transformations like Expression/Aggregator where you can hold a value of incoming data. This can be helpful when you want to check if incoming column value is same or a cha...

Answer Question  Select Best Answer