Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!
Free Trial

Forum - Deutsch

Suchen Sie nach Antworten, stellen Sie Fragen und teilen Sie Ihr Alteryx-Wissen.
GELÖST

BlockUntilDone Tool doenst work like it should

df
Asteroid

Hello i have some Problems with the "Block Until Done" Tool.

 

My test:

1) i created an Table in mysql with column "date" and "KPI"

 

df_0-1580485873032.png

2) Now i wanted to update the last to days.

i SHOULD be: search for Min Date in the Groupfunction. And Insert Into everything (again) in the table where date is smaler then min_date

After that: append the columns i wanted to updated in the table

 

But the Block 2 is done first.

df_2-1580486311009.png

What i am doning wrong?

Cheers,
David

7 ANTWORTEN 7
reply_mueller
Asteroid

Hey David,

 

the BUD-Tool will not control whether the downstream tools have finished processing. It will only guarantee, that all records have exited output anchor 1 before starting to exít output anchor 2. It is obvious, that the downstream tools on anchor 1 will take more time.

 

You can either use some workarounds like putting the BUD-tool to the very end, which does still not guarantee an orderly execution or you simply use the union tool, already assembling the records in the desired order inside of alteryx.

This is the much cleaner approach imo.

 

See the attached picture for a modified approach like yours and another more dynamic approach (if it's always the last two dates).

AlteryxGui_2020-02-02_14-06-51.png

 

 

That said, it would be even cleaner to just use a primary key in your table and only update the respective records (setting on the output data node, instead of overwrite)

 

Greetings

 

Johannes

(Blue Reply GmbH)

 

df
Asteroid

Hi Johannes,

 

thanks for the answer. Lets say i always want to update the last x days in the table an also want to append some new values. When i use update the new vaule of 2020-02-02 is not in the table. would i do this with multiple output tools?

old table

daykpi
2020-01-313
2020-02-014

 

new values

daykpi
2020-02-01 (update)3
2020-02-02 (append)6

 

Would this be a good approche?

df_1-1580722557541.png

 

 

About the block until done tool:

Then the name of that tool is very confusing. i thought that the block 1 needs to be completly finished until 2 beginns ("block 2 until 1 is (completly) done")

But i guess its just the tool directly behind the 1 anchor needs to be finished until 2 starts.

 

But i guess i dont need it anyway 🙂

Cheers,
David

 

 

 

Edit: I just see that i dont need the union tool with the two filters tools afterwords since the datastreams are already splitted the way i want before the union.

reply_mueller
Asteroid

Hello David,

 

this approach seems still a little bit difficult in my eyes. You could use something like this, but I would advise you not to use a surrogate key but the date as a primary key, since it seems, that all dates are unique in your database design.

 

This would boil down the approach drastically to a simple setup like this:

AlteryxGui_2020-02-03_16-06-39.png

 It is important to set the Output Options to Update; Insert if New, in order to produce the desired behaviour.

 

AlteryxGui_2020-02-03_16-06-53.png

 

If this workflow relies fully on user entered dates and KPIs, you could consider making an Analytic App out of it, by parametrizing the text input node, to provide a better UX.

 

I hope this helped.

 

Johannes

(Blue Reply)

df
Asteroid

Hi Johannes,

 

sanders_meme_alteryx.png

 

just remembered what the problem is.
I can't work with primary key.

The problem above was simplified (maybe bit to much)

 

Lets say i have a table like this:

DayTimeKPI
A18:434
A20:555
B17:336
B19:107
B23:318
C11:399
C23:014
.........

What now happens is that i want to "update" one day.

But the problem is that the Time and KPI changes.

 

Lets say i want to update Day "C"
What i wanted to do (first post) Is take all days - finde out which days i want to update - write the days back to the table (drop) - only days that i dont want to update are left (First Block Ends here)
Now append the dates i want to update (Second Block).

 

So i cant work with update function here since i cant work with primary keys.
(Time can change, rows can change [e.g. i had one row in Day "C" but now need to insert just one])

I guess this just works with block until done (what doesnt work for me correctly).

Or is there a way to delete days in my table via alteryx?

 

Best regards,
David

StephV
Alteryx Alumni (Retired)

Hallo @df, vielen Dank für Ihre Frage! Sie wird sicherlich auch anderen Benutzern helfen! Und danke auch an @reply_mueller für seine Antworten ! 

 

Nur dieses Forum ist jedoch in deutscher Sprache und ich lade Sie herzlich ein auf Deutsch zu schreiben. 

Wenn Sie Englisch bevorzugen, keine Sorge, können Sie Ihre Frage gerne im englischen Forum posten. 😉 

 

Vielen Dank

Steph Vitale-Havreng
reply_mueller
Asteroid

Hello @df ,

 

nice memeing-skills :), sorry for the late answer, though.

 

Okay, if you can't work with the primary key approach, let's try something else: As I told you before, in my opinion you don't need a Block-Until-Done-Tool at all. The only problem is how to address the deletes. It is possible to perform an SQL Delete-Statement from a Dynamic-Input-Tool, but it does feel a bit "hacky".

 

If the amount of data is manageable, I would actually do a TRUNCATE TABLE (Output Option: Delete Data and Append, see second screenshot), if not, we can talk about the "hacky" approach again :).

What you'll want to do is to exclude all updated days from the output. I would advise you to use a Join-Tool for this Task, keeping all unmatched records from the database and appending a unique match.

 

Pay close attention on how to configure the Join-Tool.

reply_mueller_3-1581621140969.pngreply_mueller_4-1581621154508.pngreply_mueller_5-1581621182241.png

 

I hope this solves your problems. Just text back if you stumble on further problems.

 

Best,

 

Johannes

(Blue Reply)

df
Asteroid

Hi Johannes,

 

so i tested it. i think that does not work. Let me explain why:

1) Join Tool - what should be joined? One day can have multiple times. And the Time can change between updates.

What happens here is that we cant join by time.

New Time would be in the right output.

The sample tool would ignore all the other times of a date.

df_0-1582200729333.png

But how about this one:

The Data i want to update is usally last x days.

pick all the date from database which are not in that time.

Union with the data i want to update (that are in that timeperiod)

Insert it into the database with drop.

What do you think?

And one additional question that came to my mind: is it possible to set some kind of "workflow parameter"

Like i can set this parameter to a value e.g. 14 and can use that in that workflow.

Example: in filter tool with date <= last x days (x is the paremeter)

or in input tool "Select * from table1 where date >= last x days.

 

df_1-1582200969512.png

 

Best regards,

David

Beschriftungen