Suppose I have a scenario like below:
Input:
======
Id Col1 Col2
a 100 Null
a Null 200
b 300 Null
b Null 400

And I need output as:
Output:
=======
Id Col1 Col2
a 100 200
b 300 400

How to get only Not NUll Values?

Swapnil

• Feb 9th, 2023

One component is enough for this - Rollup
Rollup Key - {id}

in transform:

out.id :: in.id;
out.col1:: sum(first_defined(in.col1,0));
out.col2:: sum(first_defined(in.col2,0));
end;

Divya

• Oct 27th, 2021

Use rollup Key : {id} transform : type temporary_type = record string("|") prev_record_col1; string("|") prev_record_col2; end; temp :: initialize(in) = begin temp.prev_record_col1 :: a...

Answer Question  Select Best Answer

saikumar

• Jun 29th, 2022

Multistage componets means transform components rollup,reformat,and scan normalize

Anjali

• Nov 19th, 2021

Multistage transform is a transform that modifies in up to five-stage - input selection, temporary initialization, processing, finalization and output selection
multistage transform components are -
rollup
scan
scan with rollup
normalize
denormalize sorted

Answer Question  Select Best Answer

I have two files and I want to compare those two records ,after comparing I want to pull out the records which are same in both records and I want the new record from the unmatched records

record format is like this:

decimal(3) CUST_ID;
string(6) CUST_NAME;
string("
") ADDRESS;


same for two files;

and...

Anand Kishore

• Oct 7th, 2022

Keep a file as lookup.
Use the syntax as lookup_match to get matching file and the rejected records are non matching record

Mahesh

• May 26th, 2021

1 Use all fields as key in in component with inner join and collect records from their respective ports
2 concat all the fields and use use field as key in in component with inner join and collect records from their respective ports
3 you can do same with lkp file

Answer Question  Select Best Answer

I have input like 1,2,3-7,8,9 How to generate output like below
1
2
3
4
5
6
7
8
9

Neelima

• Aug 25th, 2022

out :: reformat(in) = begin let int i = 0; let string("")[]dv = string_split(string_replace(in.line,"-",","),","); let int count=length_of(dv); let string("...

Anu

• Jan 10th, 2020

String_split(String_replace(( re_get_match(in.input,"-")),","),",")

After that do string join

Answer Question  Select Best Answer

I am having input column as runs with values
3
2
4
6
0
1
0
3
2
6
0
0
4
1
1
2
3

In output I want over number on Over column and corresponding runs on Runs column
Expected o/p is:-
Over Runs
1 2
1 4
1 6
1 3
1 0
1 1
2 0
2 3

Anurag

• Nov 22nd, 2022

Let decimal(2) over=0;
out::reformat(in)=
begin
if (((next_in_sequence()-1)%6)==0)over=over+1;
out.over :: over;
out.run::in.rec;
end;

Vishal

• Oct 20th, 2021

Why did you use decimal_round_down() function here? I see code can work fine without it also.

Answer Question  Select Best Answer

Input file
col1
1
2
3
4
5
6
7
8

output file
col1 col2 col3 col4
1 2 3 4
5 6 7 8

How to achieve this?

Anand Kishore

• Oct 7th, 2022

Use round robin with sequence 3

Mahesh

• May 26th, 2021

Redefine component with output dml as
record
decimal("x00") col1 = 0;
decimal("x00") col2= 0;
decimal("x00") col3 = 0;
decimal("x00") col4 = 0;
end;

Answer Question  Select Best Answer

Hi,
I have a data like 1,a,b,c,2,d,e,f,g
required output is in two fields:
field1,field2
-----------------
1,a
1,b
1,c
2,d
2,e
2,f

janshi

• Dec 27th, 2021

These are the temporary table created by sql server. When any new entry is added in main table then this entry also goes to the inserted table, similarly if any record is deleted from main table then this will go to deleted table.

Answer Question  Select Best Answer

I have a scenario:
Input file
Col1 col2
1 A
2 B
3 C
4 D

and desired output should be like below:
col1 1 2 3 4
col2 A B C D

Could you please help

Kanimozhi

• Nov 18th, 2021

Col1 col2
A 1
B 2
C 3

ravichcv85

 Profile Answers by ravichcv85 Questions by ravichcv85

• Dec 23rd, 2015

By Using "meta pivot component" - we can change the input data into
col1 1
col2 A
col1 2
col2 B
col1 3
col2 C

In order to get the desired output -

col1 1 2 3 4
col2 A B C D

we need feed the output of "META PIVOT" component to De-normalize component.

Answer Question  Select Best Answer

I have 1 TB of record with below format:

CUST_ID
CUST_NAME
GENDER
MOBILE_NO
AGE

I want to fetch max age from each Gender group by only using Reformat component. How to achieve this?

Sohil

• Sep 12th, 2021

Input->sort with Age->Partition By Roundrobin(as 1 TB of data)->Filter By Expression(GENDER==M)->2 Dedup Sort (with select & Deselect port of FBE out) with Key Blank and Keep First -> Concate -> Gather ->output File

Manish

• Jun 24th, 2021

Hi Mahesh, will this solution work if the input data file is a mfs file and we are supposed to run this in parallel?

Answer Question  Select Best Answer

how to Convert 4 way mfs to 2 way mfs ....can anyone tell me clear how to do with example

Mahesh

• May 26th, 2021

Input(4 way) --> (fan in)sort(layout 2 way)--> output( way)
I think this might work

Amaan Sajid Ansari

• Nov 14th, 2018

Gather and repartition, else your graph will throw an error.

Answer Question  Select Best Answer

Explain what .air-lock file is?
What is the difference between .sandbox.pset and .project.pset and the need of .project-start.ksh and .project-end.ksh

Mahesh

• May 26th, 2021

Any changes required only at sandbox level are stored in sandbox.pset.So a parameter value for AI_FILE in project.pset will be overridden by sandbox.pset
.air-lock as the name suggests is a ab initio repository lock file created when a lock is applied on an object for changes.

Ashiq Aashu

• Jun 4th, 2018

Data from 1 column to be separated in multiple columns

Answer Question  Select Best Answer

If I run the graph at 9 O'clock, it should pick the data with the latest updated timestamp records like from 8 to 9 O'clock updated records from the table. How to pick the delta data from specific files or tables?

Mahesh

• May 26th, 2021

1 - if its a table it should have a column with timestamp and accordingly you filter out the records
2 - if its a file again if there is a timestamp column its easier, else read the file and look it up and join on key and timestamp, anything that does not join is new.

irfan1patel1

 Profile Answers by irfan1patel1 Questions by irfan1patel1

• Jun 19th, 2017

You will definitely need a column in file or table which has the update timestamp. Based on timestamp you can simply filter the records using filter by expression

Answer Question  Select Best Answer

Mahesh

• May 26th, 2021

For data parallelism, we can use partition components. For component parallelism - its when i/ file ---> rollup(mfs layout) --> output . Here practically there are 4 copies of components running ...

Gouse

• May 26th, 2016

The component without sorted input because sort component breaks pipeline parallelism.

Thanks
Gouse

Answer Question  Select Best Answer

We have sort and sort within groups components. we can achieve the sort within group functionality by placing two keys in sort group. Then why we have to go for sort within groups?

Mahesh

• May 26th, 2021

One of the other examples could be :
graph A has done PBKS on field A
and when the output of graph A is being read by graph B it needs data to be sorted on fieldA and fieldB so major key is fieldA and minir key is fieldB.This saves time and increases performance.

sundar

• May 5th, 2015

Hey Munmun,

Do we need sorted data to sort with in group or component will taking care?

Thanks

Answer Question  Select Best Answer

How to get each partition count and total count of a multi file?

Mahesh

• May 26th, 2021

M_wc gives you the number of records in all the partitions and als a total at the bottom

Shubham

• Sep 18th, 2019

Reverse the Command... m_wc

Answer Question  Select Best Answer

How to modify record in multifile if data is ASCCI and if data is Obserddata?

Mahesh

• May 26th, 2021

To check malformed records you can use m_dump -partial option.This option shows any incomplete records.Else run the graph and attach a reject and error port to see

SP

• Dec 11th, 2017

1. View data and get the malformed record using m_dump. Note down that record number(s). 2. Using Filter by Expression, deselect only that/those record(s). 3. Using Reformat, specify values manually...

Answer Question  Select Best Answer

How to Split a 10 GB file into 10 files of 1 GB in Ab initio?

Mahesh

• May 26th, 2021

Use write multiple files component or create m_mkfs with 10 partition layout. Write data into that file with broadcast component, but might get 10 files in mkfs

Arun Ramaswamy

• Dec 9th, 2019

Why don't we use write multi files and take a approx rec space and do a split based on that.

Or keep as void of one and spilt when you write 1024*1024*1024*8.

Answer Question  Select Best Answer

I have a 4way mfs. I connected to rollup, i have mentioned key as null. How many records will come to output?

Mahesh

• May 26th, 2021

4 records, 1 per partition as rollup with be working in a component parallelism. So in the background there will be 4 processes each for a partition on which the component works.Hence 4.The question no where mentions there is a fan in happening anywhere.

Amaan Sajid Ansari

• Nov 14th, 2018

You have to understand how roll-up works, it makes group of records based on the key, If the key is null it considers all the record in a partition as one so it will throw 1 record per number of partition. In your case Its 4

Answer Question  Select Best Answer

How to add header and trailer in a file?
Header - column names
Trailer - count of the records

Mahesh

• May 26th, 2021

i/p file --> replicate --> 2 out ports -- one with data and secnd connected to rollup with no key and a count() function this gives the trailer record. Bring this flow back to a concat component as...

Suresh

• Mar 24th, 2020

There should be three input files created. one is Header file, second file is Body and the third file is the trailer. you should have three files connected to the concatenate component as inputs and it will produce the o/p file as expected.

Answer Question  Select Best Answer