I have two big tables and the database isin SQL Server 2005.
Table1 with about 25.000.000 where each row is a unique invoice and Table2 with 50.000.000 rows where each row is a item from an invoice (e.g.: 3 rows for an invoice if the costumer bought 3 different products).
When I try to concatenate the fields that I need to create a Key, in the load I got a logic memory error. I've tried everything, concatenating all fields and creating a key, changing the type of datafield, synckey, etc.
I discovered that if I use varchar(10) or less it works but anything above that doesn'twork. For me to get the right key I need a varchar bigger than 10.
The fields that are common are:
- Date (smalldatetime in SQL, used date(daystart(timestamp(date,'hh:mm:ss')))in QV)
The problem is that invoice_id isn’t unique foreach store, so I had to consider pos_id. I still got some errors in the resultsand I discovered that the database have some cases where the invoice_id isn’tunique for the same store_id and the same pos_id depending on the period (don’t ask me why), soI had to consider the date too. So my key became very big.
I tried the concatenate function in several ways like:
In Qlikview: date & store_id & pos_i & invoice_id
In the Select of SQL: cast(date as varchar) + cast(store_id as varchar(3)) + cast(pos_idas varchar(3)) + cast(invoice_id as varchar(6))
The most important table for me is Table2, but I need the information about time from table one. I was able to get it using join between the four fields. So a new column came up in Table2 with the time information:
LEFT JOIN (Table2)
The thing is I need to identify the unique invoices and if I have just one table at the end I need to be able to count distinct invoices.
Already tried too create a field in table1 with all four fields concatenated, and after that did the same process as for the time but didn't work (memory problem when it reaches a specific number of rows loaded).
It seems that when I have a table with a lot of rows, there cannot be a field with a lot of data.
Can anyone help me with a solution please?