Coming soon: We’re working on a brand new, revamped Community experience. Want to receive updates? Sign up now!

0 votes

Hi, im kinda new to DSS.

I have a huge Dataset (with 20M+ rows), this dataset has a timestamp column, and 20 other columns.

I have another Dataset with like 15 rows, this one has 3 columns start, stop and flag.

Foreach row in my first Dataset i want to check if the timestamp is between start and stop of a row in the second dataset, if it's i want to enrich the first dataset with the flag from the second. And if a row in my first Dataset isn't between any of the start and stop, i don't want to keep it.

I've done this with a Python recipe using Pandas, but im trying to do it only with DSS recipes.

I tried to do it with a Join recipe with this SQL code : 

$left.timestamp>=$right.start AND $left.timestamp<=$right.stop;

but it's not working..

I found one way but it takes too much disk space and time:  Do a cross join between my two Datasets and then do a filter with the value of start and stop

 

Can you guys suggest me a good way to do this ?

by
reopened by
Hi Fragan, what's not working? What results are you getting?
With the Custom SQL condition join ?
Im getting this error :

Job failed: Column "Test_dataset.TIMESTAMP" not found; SQL statement: EXPLAIN SELECT "Test_dataset"."timestamp" AS "timestamp", "Test_dataset"."time_to_crash" AS "time_to_crash", "Test_dataset"."nb_reqs_200" AS "nb_reqs_200", "Test_dataset"."nb_reqs_500" AS "nb_reqs_500", "testa"."id" AS "id", "testa"."start" AS "start", "testa"."stop" AS "stop" FROM "PIF_WITH_GENERATED_DATA.Test_dataset" "Test_dataset" INNER JOIN "PIF_WITH_GENERATED_DATA.testa" "testa" ON "Test_dataset".timestamp>="testa".start AND "Test_dataset".timestamp<="testa".stop [42122-195]

1 Answer

0 votes
Best answer

Hi Fragan, 

Please use the following syntax on your join clause

"Test_dataset"."timestamp">="testa"."start" AND "Test_dataset"."timestamp"<="testa"."stop" 

mind the quotes around the field names.

 

by
selected by
1,337 questions
1,362 answers
1,556 comments
11,912 users

©Dataiku 2012-2018 - Privacy Policy