Create Multiple Excel Output Files on json attribute basis

[Anonymous]

Hi all,

I am totally new for this data Integration process.

I need to read API and convert that into multiple excel files depending upon the values of one particular attribute.
for example :

{
id:1,
job:student,
Name:ABC,
Date:01,
year:2018
},
{id:1,
job:staff,
name:pr,
Date:02,
year:2019
},
{id:1,
job:student,
name:mys,
Date:01,
year:2019
}

ideally it should create two excel files one named for student and other named for staff and this needs to be generic.
Number of excels should be generated on the basis of number of job types and that is not fixed.

For now what I am doing is
rest-> extract fields-> map-> excel output

my excel looks like :
id     name      job          period
1       ABC     student    JAN-2018
2        pr         staff        JAN-2019
3      mys      student    JAN-2019

expected is :
first excel
id  student   period
1      ABC        JAN-2018
3      mys        JAN-2019

second excel
id    staff       period
1     pr         JAN-2018

another way in single excel :
id   staff   student     period
1     -         ABC        JAN-2018
3      -        mys        JAN-2019
2    pr         -          JAN-2018

I maybe doing something wrong.
please let me know if I can do anything to work it as expected.
In simple words one column needs to header and other to be value of it. and condition on basis of which it can create new row for new job profile.

Thanks in advance.

[akumar2301]

Output in one excel would be easier as you have to just change the tmap to populate output like you want

Define output scgema as
Id staff student period

Staff = if inputjobname is “staff” then inputname else -
Student = if inputjobname is “student” then inputname else -

[Anonymous]

job profile is dynamic it can be student staff worker anything which comes dynamically in API

[akumar2301 · Accepted solution]

Split the input based on column value and you could reformat after tfixedflowinput

http://talendhowto.com/2017/10/27/how-to-split-file-depending-on-column-values-2/

[Anonymous]

Hello ani11,

You can achieve this by generating files with a different name. In this case add the "job" in the fileName.

In attachment a printscreen of the data integration job.

First save your api response in your cache by using a tHashOutput.

Then loop over every record and save it to the correct file. (The iterator will decrease your performance.)

Kr,

Jelle

Screenshot_1.png