Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everybody
I’ve got a problem. Some data are double. I’ve some articles with more than one picturepath. Now i would like to aggregate them in the script. I only want to show these pictures with the highest picturenumber like max(bildnummer)
How should i do that in the script?
The actual script looks like this:
Bilder_temp:
LOAD subfield(bildname, ' ', 1) as key_Artikel ,
bildnummer,
mandid,
bildtyp,
anwendung,
replace(servername, 'N:\', '\\DM-W2-02\Bilddatenbank\') as servername,
pfad,
(replace(servername, 'N:\', '\\DM-W2-02\Bilddatenbank\')&pfad) as AnzeigePfad,
bildname,
beschreibung,
subfield(bildname, ' ', 1) as ArtikelNr
FROM
B:\Produktiv\01_Extract\03_QVD\bilder.qvd
(qvd)
where mandid = 'DM';
i'm a beginner in scripting 🙂 Thx for your Help
cheers
marco
Sorry, i change tha name of the first table
instead
Bilder_temp
to
Bilder
Or
Inner Join (Bilder_temp)
LOAD
Max(bildnummer) as bildnummer,
key_Artikel
Resident Bilder_temp
Group By
key_Artikel;
Check the raw QVD bilder.qvd for duplicate in another application and cross check weather there is always duplicate records in the QVD or not then go further.
Can you provide sample data in excel or in your sample qvw?
here is an example. In Row L you see the picture number. I only want to see the line with the highest number.
Hi, try this.
Bilder_temp:
LOAD subfield(bildname, ' ', 1) as key_Artikel ,
bildnummer,
mandid,
bildtyp,
anwendung,
replace(servername, 'N:\', '\\DM-W2-02\Bilddatenbank\') as servername,
pfad,
(replace(servername, 'N:\', '\\DM-W2-02\Bilddatenbank\')&pfad) as AnzeigePfad,
bildname,
beschreibung,
subfield(bildname, ' ', 1) as ArtikelNr
FROM
B:\Produktiv\01_Extract\03_QVD\bilder.qvd
(qvd)
where mandid = 'DM';
Bilder:
Noconcatenate
LOAD
Max(bildnummer) as bildnummer,
key_Artikel ,
mandid,
bildtyp,
anwendung,
servername,
pfad,
AnzeigePfad,
bildname,
beschreibung,
ArtikelNr
Resident Bilder_temp
Group By
key_Artikel ,
mandid,
bildtyp,
anwendung,
servername,
pfad,
AnzeigePfad,
bildname,
beschreibung,
ArtikelNr;
Drop Table Bilder_temp;
Best Regards.
Tonial.
dont work 😞 i'll upload you the qvd and the application. my test article is 11229. Sorry for that, but i have no idea in scripting 🙂
thank you verry much for your help 🙂
ok, try this.
Bilder:
LOAD
subfield(bildname, ' ', 1) as key_Artikel ,
bildnummer,
mandid,
bildtyp,
anwendung,
replace(servername, 'N:\', '\\DM-W2-02\Bilddatenbank\') as servername,
pfad,
(replace(servername, 'N:\', '\\DM-W2-02\Bilddatenbank\')&pfad) as AnzeigePfad,
bildname,
beschreibung,
subfield(bildname, ' ', 1) as ArtikelNr
FROM
bilder.qvd (qvd)
where mandid = 'DM';
Inner Join (Bilder)
LOAD
Max(bildnummer) as bildnummer,
key_Artikel
Resident Bilder
Group By
key_Artikel;
Best Regards.
Tonial.
Hi Fernando
Thank your for your help!! Awesome!!
There is an error in the Inner Join part. Do you have an idea?
Table not found
Inner Join (Bilder)
Max(bildnummer) as bildnummer,
...
...
Sorry, i change tha name of the first table
instead
Bilder_temp
to
Bilder
Or
Inner Join (Bilder_temp)
LOAD
Max(bildnummer) as bildnummer,
key_Artikel
Resident Bilder_temp
Group By
key_Artikel;
Hi Fernando
Thank you verry much. It works now!! Perfect.
Voucher for a beer 🙂