topic Re: synthetic keys in XML data load in QlikView

synthetic keys in XML data load

Fri, 28 Mar 2014 13:45:59 GMT

Hi all,

I am using qlikview for about 3 - 4 weeks now and allready came into several situation with synthetic key. Till now I was allways able to manage them but now I am a bit lost.

I am trying to load XML Data from an official webside. The XML data consists out of several tables which are joint by a manual created key. The XML data needs to be loaded for several dates. The date itself is part of the XML URL.

Since I am requesting allways the same 4 columns I would like to store them into one table in qlikview (like loading 2 Excel documents with the same columns, they get stroed by qlikview in the same table)

In the beginning I use the script wizard to load the first Information from the XML Interface, everythink was working fine. When I add the second LOAD for the second date, I get synthetic keys and I do not really know why.

Can someone help ?

Skript example:

LOAD

klasse as KLASSE,

quote as QUOTE,

jackpot as JACKPOT,

%Key_zahlenUndQuoten_429DBD973FACB660 as key // Key to parent table: TABLENAME2

FROM [https://URL....&datum=25.12.2013] (XmlSimple, Table is [TABLENAME1]);

Join

LOAD

[head/datum] as DATUM,

%Key_zahlenUndQuoten_429DBD973FACB660 as key // Key for this table: TABLENAME2

FROM [https://URL....&datum=25.12.2013] (XmlSimple, Table is [TABLENAME2]);

DROP field key;

LOAD

klasse as KLASSE,

quote as QUOTE,

jackpot as JACKPOT,

%Key_zahlenUndQuoten_429DBD973FACB660 as key // Key to parent table: TABLENAME2

FROM [https://URL....&datum=28.12.2013] (XmlSimple, Table is [TABLENAME1]);

Join

LOAD

[head/datum] as DATUM,

%Key_zahlenUndQuoten_429DBD973FACB660 as key // Key for this table: TABLENAME2

FROM [https://URL....&datum=28.12.2013] (XmlSimple, Table is [TABLENAME2]);

DROP field key;

Re: synthetic keys in XML data load

whiteline — Sun, 30 Mar 2014 09:24:04 GMT

Hi.

There are three statements that you have to notice:

By default QV concatenates the subsequent loads if they have the same list of field names.

By default the join load is done to the table above (in the script).

After the script ends QV generates the references according to field names creating synkeys if needed.

In your case the first one is not true. After the first join the subsequent load is done into separate table.

Then the last load joins to it.

As a result you have two different tables with the same field names. As a final step QV generates the keys to connect them.

Assign the names to your two tables.

Concatenate the separate table for the second date manually with additional load.

Then remove the unnecessary table.

Re: synthetic keys in XML data load

PrashantSangle — Sun, 30 Mar 2014 15:29:57 GMT

Hi,

Looking at your script, You are loading same type of data from different tables,

So you can create one table instead of several tables;

You are doing join on two tables so instead of this you can simply concatenate that table.

One more advice always try to give names to your tables.It will help you to understand flow.

do something like this,

MainTable:

LOAD

klasse as KLASSE,

quote as QUOTE,

jackpot as JACKPOT,

%Key_zahlenUndQuoten_429DBD973FACB660 as key // Key to parent table: TABLENAME2

FROM [https://URL....&datum=25.12.2013] (XmlSimple, Table is [TABLENAME1]);

Join(MainTable)

LOAD

[head/datum] as DATUM,

%Key_zahlenUndQuoten_429DBD973FACB660 as key // Key for this table: TABLENAME2

FROM [https://URL....&datum=25.12.2013] (XmlSimple, Table is [TABLENAME2]);

//DROP field key;

Concatenate

LOAD

klasse as KLASSE,

quote as QUOTE,

jackpot as JACKPOT,

%Key_zahlenUndQuoten_429DBD973FACB660 as key // Key to parent table: TABLENAME2

FROM [https://URL....&datum=28.12.2013] (XmlSimple, Table is [TABLENAME1]);

Join

LOAD

[head/datum] as DATUM,

%Key_zahlenUndQuoten_429DBD973FACB660 as key // Key for this table: TABLENAME2

FROM [https://URL....&datum=28.12.2013] (XmlSimple, Table is [TABLENAME2]);

DROP field key;

Regards,