topic Re: Possibility to extract only relevant csv data from one xml element? in QlikView

Possibility to extract only relevant csv data from one xml element?

Fri, 23 May 2014 20:01:34 GMT

Dear all, I am a student currently trying to learn more about the loading script within QlikView.

I have an xml file from a statistic database, which I can generate directly through the web.

The first problem I have is that I only want to load the data from one element <TabellenDaten>.

The data within <TabellenDaten> is csv with semicolon. The problem is that it includes the legend (key) and further descriptions, which I don't want to import.

Summing up, I want to tell QlikView :

1. To import only the data from the element <TabellenDaten>

2. To only include a part of this content

Please let me know if this description is sufficient.

It would be great if you could provide help for this problem.

I already searched this forum, but could not find any hints.

Kind regards and thank you

Thorsten

PS: I added an exemplary xml file to outline the problem

Re: Possibility to extract only relevant csv data from one xml element?

whiteline — Fri, 23 May 2014 21:19:28 GMT

Hi.

Could you add the xml sample to make the discussion more close to reality ?

Re: Possibility to extract only relevant csv data from one xml element?

Sat, 24 May 2014 07:59:21 GMT

Hi whiteline,

I added an example.

Anyone could provide help?

Kind regards

Thorsten

Re: Possibility to extract only relevant csv data from one xml element?

whiteline — Sat, 24 May 2014 09:40:08 GMT

Hi.

You can choose file type 'delimited' and skip the unnecessary header rows.

Re: Possibility to extract only relevant csv data from one xml element?

Sat, 24 May 2014 10:09:25 GMT

Hi whiteline,

thank you very much for your response. So qlikview automatically detects the element where the content is defined?

It works now, the only problem I have is that the umlauts (Ä,Ö,Ü) are detected wrong. Do I have to define that in the character set menu? Or do you accidentally know what charset I need to use?

Kind regards and thank you very much!

Re: Possibility to extract only relevant csv data from one xml element?

whiteline — Sat, 24 May 2014 13:46:54 GMT

Hi.

Not automatically. You choose the number of lines to skip. If you need some dynamic logic consider 'transformation step'.

Try different charsets, start from UTF-8, or investigate the export properties of the source system.

Re: Possibility to extract only relevant csv data from one xml element?

Sun, 25 May 2014 16:17:22 GMT

Hi whiteline,

thank you for the response!

As I'm not able to change the export properties of the sources system, I tried several charsets, but none worked.

I also tried to replace the "Ä" within the load statement - but if I do so, I have two resulting columns...

Is there a possibility to replace characters before the load statement?

I'm quite confused

Kind regards and thank you

PS: I added a screenshot of the imported table

Re: Re: Possibility to extract only relevant csv data from one xml element?

rbecher — Sun, 25 May 2014 18:04:59 GMT

Hi Thorsten,

this is a solution how I would load the envelope's data it:

Envelope:
LOAD
    [Body/TabellenExportResponse/TabellenExportReturn/tabellen/tabellen/tabellenDaten] as tabellenDaten
FROM [ExportService.xml] (XmlSimple, Table is [Envelope]);

RohDaten:
FIRST 52 LOAD @1 as Titel,
     @2 as [1995],
     @3 as [1996],
     @4 as [1997],
     @5 as [1998],
     @6 as [1999],
     @7 as [2000],
     @8 as [2001],
     @9 as [2002],
     @10 as [2003],
     @11 as [2004],
     @12 as [2005],
     @13 as [2006],
     @14 as [2007],
     @15 as [2008],
     @16 as [2009],
     @17 as [2010],
     @18 as [2011],
     @19 as [2012]
FROM_FIELD
(Envelope, tabellenDaten)
(txt, utf8, no labels, delimiter is ';', msq, header is 6 lines);

ErgebnisDaten_Tmp:
CrossTable(Jahr, Wert, 1) LOAD * Resident RohDaten;

ErgebnisDaten:
NOCONCATENATE LOAD Titel, Jahr, If(Wert='...', Null(), Wert) as Wert Resident ErgebnisDaten_Tmp;

Drop Tables Envelope, RohDaten, ErgebnisDaten_Tmp;

The only questionable thing is the amount of rows I've set fixed to 52 (First).

- Ralf

Re: Possibility to extract only relevant csv data from one xml element?

Sun, 25 May 2014 19:16:31 GMT

Great!

Thank you very very much!

Kind regards

Thorsten