select over two tables

[mathias_schnoor]

select_over_two_tables.yxmd

Hi!

I have two input-files.

The first file (alphabet) include columns A,B,C,D;E....

The second file (alphanumeric) include in a column ABC the column names of the first file as content.

I made a pivot with cross Tab Tool so I get one column with each row entry of ABC.

For example:

First CSV: (alphabet)

A;B;C;D;E

1;2;3;4;5

1;2;3;4;5

1;2;3;4;5

...

Second CSV-File:(alphanumeric)

Run-ID;ABC

100;A

200;A

300;B

400;C

400;B

400;A

...

With the Filter Tool and the Cross Tab Tool a made a functionalty like the sql syntax (for example)

select ABC from alphanumeric where RUN-ID = 400

result:

RUN-ID;new_field

400;C,B,A

Now I will the two results together.

It is possible in Alterxy to build a functionalty like the sql syntax?

select C,B,A from alphabet

result:

3;4;5

Mathias

[jdunkerley79]

You can use a transpose tool to convert the alphabet table to row based table. You can use a join tool to join the two tables.

After this a join tool can join the two tables.

Finally a summarise tool will put your answer together.

I put a simple sample together to show what I mean.

[mathias_schnoor]

Hi!

Thank you for your support.

Your solution is not completely from my perspective.

My sql query result:

Your result:

You can see the dfferents.

It is possible to get the ABC content as columns and the value content as row content of the columns?

Mathias

[jdunkerley79 · Accepted solution]

Yes, possible using a cross tab tool.

Sample attached

[mathias_schnoor]

Hi!

Thank you of your support. It works fine.

Now I add a new column "mydate" in the first input field.

I try to append this column for the result output.

But I am not pleased with my solution. Do you have solution for this problem?

Mathias