topic Re: Load data condition in QlikView

Load data condition

Fri, 20 Nov 2020 18:02:30 GMT

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.

Re: Load data condition

Anonymous — Fri, 12 Dec 2014 16:31:09 GMT

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

Re: Load data condition

shree909 — Fri, 12 Dec 2014 16:33:08 GMT

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

Re: Load data condition

Fri, 12 Dec 2014 16:40:15 GMT

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

Re: Load data condition

Fri, 12 Dec 2014 16:44:40 GMT

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?

Re: Load data condition

Fri, 12 Dec 2014 16:45:37 GMT

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

Re: Load data condition

MarcoWedel — Fri, 12 Dec 2014 16:46:45 GMT

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

Re: Load data condition

Fri, 12 Dec 2014 17:05:52 GMT

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...

Re: Load data condition

MarcoWedel — Fri, 12 Dec 2014 17:07:43 GMT

please post sample data

Re: Load data condition

maxgro — Fri, 12 Dec 2014 17:10:47 GMT

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;

Re: Load data condition

Fri, 12 Dec 2014 17:25:00 GMT

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?

Re: Load data condition

MarcoWedel — Sat, 13 Dec 2014 00:06:14 GMT

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

Re: Load data condition

Mon, 15 Dec 2014 17:27:45 GMT

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!

Re: Load data condition

Mon, 15 Dec 2014 17:31:13 GMT

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