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.

[#2]

Error message tells me, i have a circular reference...

Some sample data as required, please be aware that the csv files contain thousands of rows each:

vk2009.csv
Artikelnummer	Warengruppe
10856	601
10856	601
10856	601
10856	601
10856	601
10857	50
10927	40
10970	180
10998	451
11002	430
11075	28
11075	28
11878	997
11963	28
11989	28
11989	28
12035	28
12104	452
12105	172
12105	172
12106	93
12109	472
12109	472
12109	3
12109	3
12110	103
12110	10
12110	103
12110	10
12110	10

vk2010.csv
Artikelnummer	Warengruppe
10856	601
10856	601
10856	601
10856	601
10856	601
10856	601
10856	601
10856	601
10856	601
10856	601
10856	601
10856	601
10856	601
13292	270
13419	270
13946	997
14023	700
14023	700
14025	700
14025	700
14025	700
14025	700
14025	700
14028	351
10749	601
10749	601
10856	601
11384	28
11384	28

AlteArtikelWarengruppen.csv
Artikelnummer	Warengruppe
11861	277
11862	277
11863	212
11864	391
11866	271
11867	277
11869	361
11872	301
11875	361
11876	211
11877	212
11878	301
11880	142
11881	140
11882	140
11885	211
11892	311
11893	311
11894	475
11908	351
11915	361
11917	351
11918	361
11919	361
11920	105
11921	210
11922	210
13723	471
13724	471
13744	271
13842	155
13843	155
13946	272
14450	997
20009	277
20012	351
20013	351
20014	351
20015	351
20016	351
20017	351

Forgot to add that, I import the data from the yearly csv files with join load. Does this work for you?

[MarcoWedel]

Hallo,

one solution could be then:

mapAlteArtikelWarengruppen:
Mapping LOAD
    Artikelnummer,
    Warengruppe 
FROM AlteArtikelWarengruppen.csv (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

tabArtikel:
LOAD Artikelnummer,
     If(Warengruppe = 997, ApplyMap('mapAlteArtikelWarengruppen', Artikelnummer), Warengruppe) as Warengruppe,
     SubField(FileBaseName(),'vk',2) as vkJahr
FROM vk*.csv (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

hope this helps

Gruß

Marco

[#4]

Hello Massimo,

I was trying your suggestion today which looks promising but didn't bring me the desired result.

Included my Code and some sample data.

    AAW:

    Load Artikelnummer, Warengruppe as Warengruppe2

    FROM [AlteArtikelWarengruppen.csv] (txt, codepage is 1252, embedded labels, delimiter is ';', msq); 

    tabVK:

    LOAD

     Left(Belegdatum, 4) as Jahr,

     Mid(Belegdatum, 5, 2) as Monat,

     Artikelnummer,

     Lieferschein,

     Warengruppe, 

     Kostenträger,

     If(Buchungscode > '20',Menge,If(Buchungscode < '19',Menge*(-1))) as Menge,

     [Name-1],

     Land,

     Artikelbezeichnung,

     Vertretername,

     If(Buchungscode > '20',(((Preis/[Preis per])*(1-([Kopfrabatt]/100))*(1-([Rabatt-1]/100))*(1-([Rabatt-2]/100)))*Menge),If(Buchungscode < '19',(((Preis*(-1)/[Preis per])*(1-([Kopfrabatt]/100))*(1-([Rabatt-1]/100))*(1-([Rabatt-2]/100)))*Menge))) as Umsatz,

     IF(Buchungscode > '20',(((Preis/[Preis per])*(1-([Kopfrabatt]/100))*(1-([Rabatt-1]/100))*(1-([Rabatt-2]/100)))*Menge)-((Einstandspreis/[Preis per])*Menge),If(Buchungscode < '19',(((Preis*(-1)/[Preis per])*(1-([Kopfrabatt]/100))*(1-([Rabatt-1]/100))*(1-([Rabatt-2]/100)))*Menge)-((Einstandspreis/[Preis per])*Menge*-1))) as Bruttodeckungsbeitrag

    FROM [vk*.csv] (txt, codepage is 1252, embedded labels, delimiter is ';', msq) where Artikelnummer <> '14001'; 

    Left Join (tabVK) 

    Load Artikelnummer, 997 as Warengruppe, Warengruppe2;

    final:

    NoConcatenate

    Load Artikelnummer, alt(Warengruppe2, Warengruppe) as Warengruppe

    Resident tabVK;

JOIN LOAD Kundenname as [Name-1],

     Kundengruppe

FROM

[Kundengruppen.csv]

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

You might find the mistake, that I couldn't find...

Thank you very much for your support!

[#5]

Hallo Marco,

I tried quite some variations of your solution, but still didn't manage do remove the circular reference and thus couldn't make it work...

I left the subfield reference away as i don't need the difference associated with the year I thought having: Artikelnummer, Warengruppe old, and Warengruppe new if Warengruppe old = 997

would be enough, but I still get all Warengruppen for each Article...

Gruss zurück,

Pascal