topic Why do I get a synthetic key? in QlikView

Why do I get a synthetic key?

Sat, 15 Jan 2011 00:29:38 GMT

Hello,

I have the following script:

Pre_Order_Head:
LOAD
FOKID,
FTGID,
KundNr,
Faktureringssätt,
OrderNrIn,
OrderNrKvar,
OrdersättBenämning,
Order_OrdersättKod,
Order_Orderstopp,
Order_OrderstoppKod,
Ordertyp
FROM ..\..\QVD\PCD\ExportQVD\2_Transform\Order.qvd (qvd);

NOCONCATENATE load
*
resident Pre_Order_Head
ORDER BY OrderNrIn;

store * from Pre_Order_Head into ..\..\QVD\PCD\ExportQVD\2_Transform\Pre_Order_Head.qvd;
drop table Pre_Order_Head;

Pre_Order_Head_2:
LOAD
If(rowno() = 1 OR OrderNrIn > previous(OrderNrIn), FOKID) as FOKID,
If(rowno() = 1 OR OrderNrIn > previous(OrderNrIn), FTGID) as FTGID,
If(rowno() = 1 OR OrderNrIn > previous(OrderNrIn), KundNr) as KundNr,
If(rowno() = 1 OR OrderNrIn > previous(OrderNrIn), Faktureringssätt) as Faktureringssätt,
If(rowno() = 1 OR OrderNrIn > previous(OrderNrIn), OrderNrIn) as OrderNrIn,
// If(rowno() = 1 OR OrderNrIn > previous(OrderNrIn), OrderNrKvar) as OrderNrKvar,
If(rowno() = 1 OR OrderNrIn > previous(OrderNrIn), OrdersättBenämning) as OrdersättBenämning,
If(rowno() = 1 OR OrderNrIn > previous(OrderNrIn), Order_OrdersättKod) as Order_OrdersättKod,
If(rowno() = 1 OR OrderNrIn > previous(OrderNrIn), Order_Orderstopp) as Order_Orderstopp,
If(rowno() = 1 OR OrderNrIn > previous(OrderNrIn), Order_OrderstoppKod) as Order_OrderstoppKod,
If(rowno() = 1 OR OrderNrIn > previous(OrderNrIn), Ordertyp) as Ordertyp,
If(rowno() = 1 OR OrderNrIn > previous(OrderNrIn), 1) as Order_Head_Counter
FROM ..\..\QVD\PCD\ExportQVD\2_Transform\Pre_Order_Head.qvd (qvd);

When it is done I get a synthetic key between Pre_Order_Head_2 and Pre_Order_Head-1.

Anyone have any idea as to why?

For some reason the "drop table Pre_Order_Head;" removes the table but it seems it is still there.

If I create a new application and move the second part there, i.e.

then there are no synthetic keys.

Anyone know why?

Martin

Why do I get a synthetic key?

johnw — Sat, 15 Jan 2011 01:03:24 GMT

When you told it to noconcatenate load, it did exactly what you told it to do - create a NEW table, and don't concatenate onto the old table. You didn't give this new table a name, so it decided to name it Pre_Order_Head-1 to distinguish it from the original table. Then you dropped Pre_Order_Head. It did, but that left a sorted copy of it still out there under the Pre_Order_Head-1 name. After your next load, it had two tables sharing a lot of fields, so it built a synthetic key.

In the second load, you didn't have that original table to connect up to, so no synthetic key.

I assume what you wanted to do was to sort Pre_Order_Head, and store the sorted copy. If you can't do an order by during the load (I don't remember if you can), then give the second table an explicit name, store IT to the new QVD, and drop BOTH tables when you're done. THEN load Pre_Order_Head_2.

Why do I get a synthetic key?

Sat, 15 Jan 2011 14:27:22 GMT

Thanks for your reply, I will try it tonight but I'm sure that it will work.

One question though, if QV creates a new table why can I not use "drop table Pre_Order_Head-1" to get rid of it?

When I do this QV replies that the table does not exist.

Martin

Why do I get a synthetic key?

johnw — Mon, 17 Jan 2011 18:40:44 GMT


martnorm wrote: One question though, if QV creates a new table why can I not use "drop table Pre_Order_Head-1" to get rid of it?
When I do this QV replies that the table does not exist.

I don't know. I would have expected that to work. On the other hand, I never have any tables created "accidentally" and all of my tables always have explicit names. So I've never even tried to drop a table that QlikView named for me. I can't imagine why it wouldn't let you drop it anyway, but maybe there's some strange internal restriction, or just some bug.

Hmmm, just tried it, and it worked fine for me. One possible problem is that you'd need to put the table name in brackets or double quotes due to the "-" character, though that causes a different error message, so probably isn't your problem. Here's the bit of working example I used:

NOCONCATENATE LOAD *
RESIDENT BudgetAndSales;
DROP TABLE [BudgetAndSales-1];

Why do I get a synthetic key?

Mon, 17 Jan 2011 23:30:06 GMT

From what I have seen, the DROP TABLE command only works on explicit names. I assume these are stored in different system type formats. In the end, we use them the same way.