Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Why do I get a synthetic key?

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.

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);

then there are no synthetic keys.

Anyone know why?

br

Martin

1 Solution

Accepted Solutions
johnw
MVP
MVP

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.

View solution in original post

4 Replies
johnw
MVP
MVP

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.

View solution in original post

Not applicable
Author

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.

br

Martin

johnw
MVP
MVP


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];

Not applicable
Author

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.