Load data condition

[#1]

Hello community,

I load data from several csv files also containing this data:

Artikelnummer	Warengruppe
123456	997
123457	3
123458	5
123459	4
123460	4
123461	997
123462	1

Warengruppe '997' is invalid, so I have to replace all Artikelnummer containing '997' by a Warengruppe from another csv file. This file contains only Artikelnummer that are '997' and the 'new' Warengruppe.

For example:

Artikelnummer	Warengruppe
123456	2
123461	3

Tried several things with add load, replace load, not exists, exists but couldn't find a solution:

Replace Load

Artikelnummer,

Warengruppe

FROM

AlteArtikelWarengruppen.csv

(txt, codepage is 1252, embedded labels, delimiter is ';', msq)

where Warengruppe = 997;

Must have a knot in my thinking, but i basically erase all Warengruppe that are not contained in that file.

Please help! Thank you in advance.

[Anonymous]

If Warengruppe '997' is invalid, exclude it on Load (whereWarengruppe!='997').

[shree909]

Hi,

If you want to replace the  " Warengruppe from another csv file. ". Load another csv file as applymap (load only wat u want to replace)

and use this on the main file to replace the  number.

Thanks

[#4]

I acutally need to transform it, so what was 997 is now e.g. 2

[#5]

Hi, sorry I'm new here - just checked the help and need to study a little more on that... So i use mapping load, and then i apply the map to all values where Warengruppe = 997? Correct?

[#6]

or 4 or 3, depending on the csv table, this table contains the correct Warengruppe.

[MarcoWedel]

Hallo,

one solution might be:

mapErsatzWarengruppe:
Mapping LOAD *
FROM ErsatzWarengruppen.csv (...);

tabArtikel:
LOAD Artikelnummer,
        If(Warengruppe = 997, ApplyMap('mapErsatzWarengruppe', Artikelnummer), Warengruppe) as Warengruppe
FROM AlteArtikelWarengruppen.csv (...);

hope this helps

Gruß

Marco

[#8]

Thank you, that looked very good to me, but didn't work...

I start the script with opening the mapping table, then i open plenty more csv files importing the data for the past years, checking if one value contains Warengruppe 997, if not it should keep the value imported from the csv table.

mapErsatzWarengruppe:

Mapping LOAD

    Artikelnummer,

    Warengruppe 

FROM AlteArtikelWarengruppen.csv (...);

LOAD

...

     Artikelnummer,

     If(Warengruppe = 997, ApplyMap('mapErsatzWarengruppe', Artikelnummer), Warengruppe) as Warengruppe,

...

FROM vk2009.csv (...);

LOAD

...

     Artikelnummer,

     If(Warengruppe = 997, ApplyMap('mapErsatzWarengruppe', Artikelnummer), Warengruppe) as Warengruppe,

...

FROM vk2010.csv (...);

Sounded logical but didn't work...

[MarcoWedel]

please post sample data

[maxgro]

a:

load * inline [

Artikelnummer, Warengruppe

123456, 997

123457, 3

123458, 5

123459, 4

123460, 4

123461, 997

123462, 1

];

left join (a)

load Artikelnummer, 997 as Warengruppe, Warengruppe2 inline [

Artikelnummer, Warengruppe2

123456, 2

123461, 3

];

final:

NoConcatenate

load Artikelnummer, alt(Warengruppe2, Warengruppe) as Warengruppe

Resident a;

drop table a;