topic Re: multiply the rows with dates in Talend Studio

multiply the rows with dates

Anonymous — Sat, 16 Nov 2024 07:33:14 GMT

Hi everyone. I'm new with talend open studio . I have some trouble when using it. Is there any way to I can get my expected output from this input?
INPUT
DATE (yyyymmdd) | ip | FINISHED ( 1:finished; 0: not finish)
2017-01-01 | 111 | 1
2017-01-01 | 222 | 1
2017-01-02 | 333 | 1
2017-01-03 | 444 | 1

EXPECTED OUTPUT
DATE (yyyymmdd) | ip | FINISHED ( 1:finished; 0: not finish)
2017-01-01 | 111 | 1
2017-01-01 | 222 | 1
2017-01-01 | 333 | 0
2017-01-01 | 444 | 0
2017-01-02 | 111 | 1
2017-01-02 | 222 | 1
2017-01-02 | 333 | 1
2017-01-02 | 444 | 0
2017-01-03 | 111 | 1
2017-01-03 | 222 | 1
2017-01-03 | 333 | 1
2017-01-03 | 444 | 1

Can someone show me the way to do it?
Your help is very much appreciated.
Regards,

Thang

Re: multiply the rows with dates

fdenis — Mon, 01 Oct 2018 10:33:02 GMT

hi,
use your fist file as source (3 times)
one for date tfileInput --> taggregate
one for ip tfileInput
one for done status tfileInput

use tmap to link all 3 input.
main is date lockup (all match match on nothing Full Join) to Ip
lockup to don match on Ip

add condition on the Finiched output to compare date

good luck

Re: multiply the rows with dates

Anonymous — Mon, 01 Oct 2018 11:03:36 GMT

hey fdenis . How can I fist file when it all store in one excel file so I have already linked excel file through tmap then take 3 cols I need.

Then I linked tmap main to taggregaterow ( I don't see taggregate available in my version so I used taggregaterow instead) but when I linked taggregaterow main to the next tmap(I called it tmap_6) , i couldn't link anything main to the tmap_6. Could you add some images? I'm new so it's hard for me to follow your instruction.

Looking forward to your responce

Regards,

Thang

Re: multiply the rows with dates

fdenis — Mon, 01 Oct 2018 12:54:21 GMT

I'm sorry I cannot add pics.
extract data from your excel store data split dataset by 3 with tmap (using previous message) store data (in temp files tFileDelimitedOutput or thashoutput).
then use This 3 files to link them together.

Re: multiply the rows with dates

Anonymous — Tue, 02 Oct 2018 03:23:14 GMT

hi fdenis. I still can link all 3 files to tmap . Just can connect 1 by main

Re: multiply the rows with dates

Anonymous — Tue, 02 Oct 2018 03:41:32 GMT

this is my current etl format. I followed some intruction online to link 3 tfileoutputdelimited but none of them worked

Screenshot from 2018-10-02 09-39-49.png

Re: multiply the rows with dates

fdenis — Tue, 02 Oct 2018 09:01:54 GMT

first you have to build your 3 delimited files.
tFileExcelInput --> tMap --> tFileDelimited1
--> tFileDelimited2
--> tFileDelimited3

Re: multiply the rows with dates

Anonymous — Tue, 02 Oct 2018 09:31:23 GMT

After I divined my files into 3 seperate files.I connect it using just like you said then the first time I met this error lol

Screenshot from 2018-10-02 15-22-11.png
Screenshot from 2018-10-02 15-29-49.png

Re: multiply the rows with dates

fdenis — Tue, 02 Oct 2018 09:43:07 GMT

so you are new in talend…
have a look to https://www.talendbyexample.com/my-first-talend-job.html
and convert this sample to your job
one excel to one csv first
after, add other output files

Re: multiply the rows with dates

Anonymous — Tue, 02 Oct 2018 09:47:32 GMT

thanks man.I'll try to figure it after your instruction. Anything else I will post it later at a new topic

Re: multiply the rows with dates

Anonymous — Tue, 02 Oct 2018 10:02:06 GMT

hi fdenis. Did you mean join like this in tmap?

Screenshot from 2018-10-02 16-01-34.png

Re: multiply the rows with dates

fdenis — Tue, 02 Oct 2018 10:05:27 GMT

yes you are on the good way

Re: multiply the rows with dates

Anonymous — Tue, 02 Oct 2018 10:09:34 GMT

From that step what shoud I do next fdenis?

Re: multiply the rows with dates

fdenis — Tue, 02 Oct 2018 11:01:22 GMT

you have to link row 13 and row 14 to row 15
then on the output hoan_thanh write:
(comparDate(row15.finished_Timestamp, row15.finished_Timestamp)>0)?"1":"0"

timestamp have to be Date Type

Re: multiply the rows with dates

Anonymous — Tue, 02 Oct 2018 11:19:37 GMT

it runs but all rows in col Hoan_thanh ( which is finished) all in 0

Re: multiply the rows with dates

fdenis — Tue, 02 Oct 2018 12:18:37 GMT

(comparDate(row15.finished_Timestamp, ~~row15~~.finished_Timestamp)>0)?"1":"0"

(comparDate(row15.finished_Timestamp, row13.finished_Timestamp)>0)?"1":"0"

it will be better

Re: multiply the rows with dates

Anonymous — Wed, 03 Oct 2018 06:09:19 GMT

Hi fdenis

I followed your instruction then linked it but unfortunately when I linked it but row15.finished_timestamp still get null in that row ( I checked the same data pattern, compare with just yyyy-MM-dd ). the output full of 2 and of course I did'n change anything in tmap setting)

Picture bellow

Screenshot from 2018-10-03 11-49-44.png
Screenshot from 2018-10-03 11-50-00.png
Screenshot from 2018-10-03 12-05-18.png
Screenshot from 2018-10-03 12-07-30.png
Screenshot from 2018-10-03 12-08-29.png

Re: multiply the rows with dates

fdenis — Wed, 03 Oct 2018 08:16:07 GMT

do not link date in tMap, only id.
date are managed to define if it's finished or not .

Re: multiply the rows with dates

Anonymous — Wed, 03 Oct 2018 08:57:49 GMT

hi fdenis,

You asked me to link row13 into row15. Row13 contains date . If I don't link it in row15 don't have any date so date contains will be null. Should I add a column finished_timestamp into the finished csv?

Re: multiply the rows with dates

Anonymous — Wed, 03 Oct 2018 09:22:29 GMT

hi fdenis

Like you said , I didn't like row13 into row15 . However, I add to file finished a col wich is finished_timestamp than in the output just like in picture but the file still all in 2.In file data I sure it has the date. I format the date in data pattern just like the formate in file .

Which step did I do wrong?

Screenshot from 2018-10-03 15-22-18.png