topic CONCATENATE in QlikView

CONCATENATE

Mon, 02 Nov 2009 22:09:00 GMT

I HAVE A PROBLEM WITH THE CONCATENATE ACTION. I´M TRYING TO CONCATENATE TWO QVD, WITH THE SAME NUMBER AND NAME OF FIELDS. I´M RELOADING WITH LIMITED LOAD OF 10. THE RESULT OF THIS CONCATENATE, THROW ME 30 REG. SOMEBODY CAN EXPLAIN ME, WHY DO I HAVE 30 AND NOT 20 REG? THANKS. LUCIANO STRAFACE

CONCATENATE

Mon, 02 Nov 2009 23:11:03 GMT

can you post your script, so we can have a look?

Rey-man

CONCATENATE

Mon, 02 Nov 2009 23:20:38 GMT

TEMP:

LOAD

MAKEDATE(LEFT(Fecha,4), MID(Fecha,5,2), RIGHT(Fecha,2)) AS Fecha

FROM

maestrousu.qvd (qvd);

concatenate

LOAD

Fecha

FROM

VISA_MAESTRO_USUARIOS_HIST_CITI.QVD (qvd); THANKS

CONCATENATE

Tue, 03 Nov 2009 00:14:52 GMT

Try this

TEMP:

LOAD
MAKEDATE(LEFT(Fecha,4), MID(Fecha,5,2), RIGHT(Fecha,2)) AS Fecha
FROM maestrousu.qvd (qvd);

concatenate (TEMP)

LOAD
Fecha
FROM VISA_MAESTRO_USUARIOS_HIST_CITI.QVD (qvd);

Talha

CONCATENATE

Tue, 03 Nov 2009 00:30:57 GMT

I try and I have the same result. If I change de order of the load, I have 20 reg. I dont understand why.

Thanks

Luciano

CONCATENATE

Anonymous — Tue, 03 Nov 2009 01:10:51 GMT

Can't se from the script that the number of records is limited on load.
What I'm missing?

CONCATENATE

Oleg_Troyansky — Tue, 03 Nov 2009 01:29:18 GMT

perhaps the script is being run via Debugger... it might be a glitch in the debugger and the "limited load"
function. Try loading the whole table ans see if you get the same problem.

CONCATENATE

Tue, 03 Nov 2009 01:39:58 GMT

I load the whole table and I dont have the problem. But the size of the qvd is bigger than logic.

I have this two qdv:

VISA_MAESTRO_USUARIOS_HIST_CITI.QVD

1679000 kb

maestrousu.qvd

220406 kb

And the size of the qvd resulting its 3031737 kb. Is this normal?

CONCATENATE

Oleg_Troyansky — Tue, 03 Nov 2009 01:44:49 GMT

Well, it's hard to operate with KB instead of the number of rows...Would be better if you could get the row counts from all the QVD files and compare them.

However, it doesn't sound right. Examine your script once again:

1. Do you have the same statement repeated twice by mistake?

2. Do you have a previous statement that loads the same set of fields, causing unexpected concatenation?

cheers,

CONCATENATE

Tue, 03 Nov 2009 01:59:52 GMT

The number of rows of the qvd resulting its the same that if I add the number of rows of the two qvd that I´m trying to concatenate.

The names of the fields are the same in the original qvds.

The number of the fields are the same in the original qvds.

Do you know why is this happening?

CONCATENATE

Anonymous — Tue, 03 Nov 2009 02:32:58 GMT

This is how CONCATENATE is supposed to work. If you N records in one table, and M records in another, you get N+M records after you concatenate them.

CONCATENATE

Tue, 03 Nov 2009 03:06:27 GMT

Thanks, I know how concatenate supposed to work. I have:

A.QVD

5 ROWS

1.6 GB

B.QVD

2 ROWS

0.2 GB

When I concatenate both qvd, I have:

C.QVD

7 ROWS

3.03 GB

I think is not correct the size of the resulting qvd. Do you believe the opposite? QVD A and qvd B, have the same structure.

In the first post I write that the concatenate of two qvds, limiting de load in 10 rows, throw me 30 rows. Then it is not working well the concatenate function.

CONCATENATE

Oleg_Troyansky — Tue, 03 Nov 2009 03:48:49 GMT

It does sound logical that the same number of rows, when stored into a QVD file, should result in the same (or comparable) QVD size. However, QVD is a proprietary format, optimized for the fastest retrieval of the data. It's very difficult to predict the size of the resulting QVD, because the storage strategy is determined by the QlikView's internal algorithm, with the only goal of optimizing retrieval time. I'll give you a few possible reasons:

1. Maybe the existing QVD files were stored with the previous version of QlikView and maybe the algorithms had changed since?

2. Maybe one of your existing QVD files had only numeric values and the other file has alphanumeric values (or maybe much longer values), and as a result, QlikView decided to increase the length of the field in the resulting table, so that each value now takes up more space?

3. I never checked if the setting "Save Format" in document properties affects the saving of the QVD files... If it does - maybe you saved the first QVD with the higher compression? This is a long shot, but who knows...

Bottom line - if the number of rows is the same, then everything is OK. Why worry about the size of the QVD files? Storage is cheap today... The cost of additional 3 GB isn't worth your time asking the question and my time answering it...

cheers,

CONCATENATE

Tue, 03 Nov 2009 04:15:32 GMT

Thanks you and forgiveness for waste your time. I am new with this application, and cases like this make me doubt.

Greetings

Straface Luciano