topic Re: Concatenating data from an Excel sheet in QlikView

Concatenating data from an Excel sheet

datanibbler — Tue, 07 May 2013 13:31:09 GMT

Hi,

I have messed up the results of my current document and I did not notice it right away - so I have to take a few steps backward.

Currently I am facing the following issue: I have a LOAD statement from a qvd file in my script with a WHERE clause at the end - nothing out of the ordinary there, it would seem. Then I have a CONCATENATE and another LOAD statement (I deleted the keyword >Directory;< above that) from an Excel file that contains only 9 records - but the script_execution dialog window tells me that from this Excel file, QlikView retrieved the same number of records as from the qvd file (+9).

When I leave out the CONCATENATE, the number of rows is correct, but then I have an issue with a synthetic key.

Can somebody tell me why this is happening and how I can fix it?

Thanks a lot!

Best regards,

DataNibbler

Re: Concatenating data from an Excel sheet

anandathome — Tue, 07 May 2013 14:26:58 GMT

Hi Friedrich,

Could you please tell with example what did you really change in the script?

Attached example is working fine.

Re: Concatenating data from an Excel sheet

datanibbler — Tue, 07 May 2013 15:06:24 GMT

Hi Anand,

the issue I was concerned about is actually "solved" - one could say it never existed in the way I saw it. I just looked at only the first two ciphers of a number and it appeared wrong. When looking at the entire number, it is right.

The other issue, however, remains. I have just rerun the script: I am loading a qvd file with 1.5mio records approx. and I have an Excel file with just 9 records - the first is from our Database and I calculate (variable) turnover based on it, the second holds the 9 fixed_cost_items there are.

In the script, I have the first LOAD statement closed with a semicolon. Then comes the keyword CONCATENATE and the second LOAD statement without the >Directory;< above.

What should be happening is that the QlikView script loads the 1.5mio records from the qvd and then concatenates the 9 records from the Excel file. Instead, I get 1.5mio records from the qvd - and then another 1.5mio from the Excel file (exactly 9 records more).

The Excel file (attached) has the same nr. of fields as the qvd of course, but primarily it has:

- the keyfield %ITEM_NUMBER (the keyfield linking that dimension table to my main facts table),

- two fields ("Zuordnung" and "Umsatzart") which have the same name as two fields in the qvd (so the contents of the Excel file will be seamlessly integrated
and the same drilldown_dimension will work for both datasets)

- and one extra field ("Fixkosten") that holds the amounts - but only for these 9 records - in the qvd, I have added that field, but with a numeric 0 in every record.

So why is QlikView blowing up the Excel file to the same size as the qvd that it is concatenated to?

Thanks a lot!

P.S.: OK - I just saw that in your file, the same thing is happening - the script load 7 records from the qvd and 10 from the Excel file. So I guess this is normal behaviour?

Re: Concatenating data from an Excel sheet

datanibbler — Tue, 07 May 2013 15:27:18 GMT

Hi Anand,

I have just realized what is happening: It seems to be connected to those two fields having the same fieldname.

What is happening now is that when from the uppermost level of my drilldown group (turnover_type), where the variable and fixed turnover_types are neatly displayed I go down to the next detail level ("Zuordnung"),

=> QlikView mixes up those two datasets (well, it is actually one now since I have concatenated it) and, regardless whether I have selected "variable" or "fixed", it displays the values from both turnover_types - which is should not.

I chose to give the field (in the fixed_cost_table) the same name as one field in the qvd because I don't know how to incorporate a query in my drilldown group - is there a way I can include sth. like an IF clause in the drilldown group (like >> IF(Umsatzart='Fix', [Field1], [Field2]) <<)?

Best regards,

DataNibbler

Yes, I can - it didn't work before, but I have changed a number of things since and now it works. This is m.o.l. solved then - the multiplication of records is expected behaviour connected to those keyfields?