topic Re: missing records when using concatenation in QlikView

missing records when using concatenation

qlikmpate0 — Wed, 14 Oct 2015 12:03:13 GMT

I'm using concatenation to create the data I need, but pre concatenation I have the records I need to get the final figure, but when I use concatenation, some of my records are missing giving me different totals. When I add a field I then get the totals I need. Although the field I add is not a key field. Can any one help or has anyone experienced this?

regards

Re: missing records when using concatenation

swuehl — Wed, 14 Oct 2015 12:05:35 GMT

It would be easier to help you if you post at least your script code, or even better a small sample QVW that demonstrates your issue.

Re: missing records when using concatenation

avinashelite — Wed, 14 Oct 2015 12:08:43 GMT

I think you might have the similar set of data with same values associated with it . so when new field is added its able to identify the same. Please share the sample app

Re: missing records when using concatenation

qlikmpate0 — Wed, 14 Oct 2015 12:11:09 GMT

I can't send sample data as this would be against company ethics. can you advise of how to send sample data, do I do a simple reload, for only a number of records?

Re: missing records when using concatenation

avinashelite — Wed, 14 Oct 2015 12:17:14 GMT

No, need to send the actual data, create a mock of it like columns A, B and sample data . Its just of us to visualize the data pattern and issue with it

Re: missing records when using concatenation

avinashelite — Wed, 14 Oct 2015 12:36:57 GMT

I did get the field or chart your referring to

Re: missing records when using concatenation

swuehl — Wed, 14 Oct 2015 12:49:26 GMT

You are doing a

Concatenate(Unshipped)

Load

Distinct

[Join to Comparatives],

Brand as [Brand New],

//[Comp Mngmt Resp] as [Mngmt Resp],

[Comp Mngmt Resp] as EBS_Extract_XXFOL_UnShipped_All.H_MANAGEMENT_RESPONSIBILITY, // THIS HAS BEEN USED ON OBJECTS RATHER THAN Mngmt Resp!

[Comp Period Name] as [PeriodName],

[Comp Type] as Type,

'Backfilled' as UnShipRecType

Resident Comparatives

Where not Exists([Join to Comparatives TMP], [Join to Comparatives]);

A DISTINCT LOAD will also affect the table you are concatenating to. Hence might reduce rows (unless you are using a distinct key field like potentially your delivery_detail_id).

Re: missing records when using concatenation

qlikmpate0 — Wed, 14 Oct 2015 13:17:32 GMT

The field is EBS_Extract_XXFOL_Unshipped_ALL delivery_detail_id, and the object is the Volume CM comparison.

delivery_detail_d was not being used initially only when I conflicting figures that is when I added this field in and all the figures seem to be correct. My question is why would something like one field cause the data to be correct when added. When the initial load creates the records and then the new records are added(concatenated to the existing. How can those records not be included in the summation.

Re: missing records when using concatenation

qlikmpate0 — Wed, 14 Oct 2015 13:26:18 GMT

I'm not using the delivery_detail_id as a distinct key only the Brand, mgmnt resp, period name and type.

Re: missing records when using concatenation

swuehl — Wed, 14 Oct 2015 13:37:02 GMT

Not sure what you are talking about in your last comment.

I think your issue is related to the LOAD DISTINCT in your concatenate'd LOAD, which affects also the first table.

Re: missing records when using concatenation

qlikmpate0 — Wed, 14 Oct 2015 14:03:54 GMT

My Solution to this was to use group by rather than distinct as swehl suggested. the distinct is using the distinct records from the unshipped table and then concatenating the records on to that table which then does a distinct hence losing the records that I need.

Thank you for all your help.

regards