Transform table with values in EUR or Document Currency in Data Loader

[Goncalo]

Hi Experts,

I have a table that stores purchase orders data. This table has rows per PO document and item number and a column with the value ordered.

Depending on the currency of the PO, I may have two rows per PO: I always have one row with values in EUR but if the PO currency is different than EUR, I also have a row with the value in that currency. Something like this:

 

I need to transform this table in order to have one row per PO. Something like this:

Any suggestion on how to create such a table using the data loader?

Thanks in advance for your support.

[vinieme12]

Group the data while loading, as below

Summary:

Load PONumber

,Sum(if(Currency='EUR',Value)) as ValueInEUR

,Sum(if(Currency='USD',Value)) as ValueInUSD

From SomeSource

Group by PONumber;

[Goncalo]

Hi Vineeth,

Many hanks for the suggestion! That works for the values in EUR, but for the PO currency is incomplete.

The PO currency may be EUR or any other. If it's the primer, then the value will be the same as in the column in EUR. If it's the latter, I need to see the value in the currency different than EUR.

I also need to identify the currency: if I can see more than one currency per PO, then it is the one different than EUR, if it is only EUR, then it is EUR.

[vinieme12]

"The PO currency may be EUR or any other"

hi, the sample you posted doesn't specify   which currency is the PO currency, there are no indicators 

for example 1001 , which field specifies USD is doc currency and not EUR.

do you only have 3 field? can you post a full sample that best represents your actual data

[Goncalo]

I know the example above is simple, but this is the information I have.

• The PO currency may vary, may be EUR, USD, etc.
• However, the dataset I have access to, converts the value to EUR when it's different than EUR
• This generates two rows for the same document when the currency is different than EUR.

Two examples taken from Qlik.

PO 4500328073 currency is EUR, so I only have 1 row per item. PO 4500445416 currency is USD, which generates two rows: one in the original currency and a second one converted to EUR.

I need to have specific columns, one in PO currency, and another one in EUR.

Thanks for your support.