topic Re: Synthetic keys while loading XML data in QlikView

Synthetic keys while loading XML data

Fri, 28 Mar 2014 09:55:30 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 while loading XML data

Anonymous — Sat, 29 Mar 2014 04:43:05 GMT

QlikView will automatically Concatenate the tables if the Field names are the same.

I suspect the problem is with the first Drop Field key; statement. Remove that and see what happens.

Use Drop Field Key; after you have loaded all the tables.

Re: Synthetic keys while loading XML data

iktrayanov — Sat, 29 Mar 2014 04:43:07 GMT

Try this

Table1:

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(Table1)

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;

Table2:

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(Table2)

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;

Table3:

Load * resident Table1;

concatenate(Table3)

Load * resident Table2;

Drop table Table1;

Drop table Table2;