Qlik Community

Ask a Question

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

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

Vizlib Head of R&D

View solution in original post

8 Replies
Master II
Master II

Hi.

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

Not applicable

Hi whiteline,

I added an example.

Anyone could provide help?

Kind regards

Thorsten

Master II
Master II

Hi.

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

Not applicable

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!

Master II
Master II

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.

Not applicable

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

MVP & Luminary
MVP & Luminary

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

Vizlib Head of R&D

View solution in original post

Not applicable

Great!

Thank you very very much!

Kind regards

Thorsten