topic Re: Re: Double data in QlikView

Double data

Wed, 30 Apr 2014 11:15:19 GMT

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

Re: Double data

its_anandrjs — Wed, 30 Apr 2014 11:22:22 GMT

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.

Re: Double data

MK_QSL — Wed, 30 Apr 2014 11:22:25 GMT

Can you provide sample data in excel or in your sample qvw?

Re: Re: Double data

Wed, 30 Apr 2014 11:31:00 GMT

here is an example. In Row L you see the picture number. I only want to see the line with the highest number.

Re: Double data

fernando_tonial — Wed, 30 Apr 2014 11:34:55 GMT

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.

Re: Re: Double data

Wed, 30 Apr 2014 11:47:33 GMT

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 🙂

Re: Re: Re: Double data

fernando_tonial — Wed, 30 Apr 2014 12:19:30 GMT

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.

Re: Re: Re: Double data

Wed, 30 Apr 2014 12:34:38 GMT

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,

...

Re: Re: Re: Double data

fernando_tonial — Wed, 30 Apr 2014 12:46:48 GMT

Sorry, i change tha name of the first table

instead

Bilder_temp

Bilder

Inner Join (Bilder_temp)  
LOAD    
  Max(bildnummer) as bildnummer,    
  key_Artikel  
Resident Bilder_temp
Group By    
  key_Artikel;

Re: Re: Re: Double data

Wed, 30 Apr 2014 13:11:47 GMT

Hi Fernando

Thank you verry much. It works now!! Perfect.

Voucher for a beer 🙂