I'm fairly new to QlikView and this is my first post in the forum, so please forgive me if this has been asked before. I have looked but can't find a related topic I'm doing a simple table chart and summing a sales total
Client PartnerID Sale Amt Note A 231 £1.40 Note A A 231 £1.40 Note B B 542 £3.50 Note C C 734 £1.00 Note D Total £???
I've joined several SQL Tables together and aiming to replicate a report we have in Crystal, the plan is to show the sales figures and the TOP 2 Communication (Notes) regarding that sale. Currently with the set up above Client A has their sales figures added twice, even though it is the same sale, thus giving me a total of £7.30. When in fact I'm after a total of £5.90 Within Crystal I could ask it to suppress duplicate entries, but I don't seem to be able to do anything like that in QlikView. Am I missing something there, or is it not actually possible?
I have made a few assumptions here, firstly that the key is 'Client' ie represents the duplicates to look for and that when deduplicating the sales value you still want to retain the individual 'Note'.
Take a look at the following. The key thing here is that in your source data which I have dummied with an inline file, that 'Client' is changed to another name so that the exists test doesnt find itself! It also results in the (concatenated) notes being stored in a seperate table where 'Client' links it to 'Sales'.
Source: LOAD * INLINE [ Source Client, PartnerID, Sale Amt, Note A, 231, £1.40, Note A A, 231, £1.40, Note B B, 542, £3.50, Note C C, 734, £1.00, Note D ];
[Sales]: LOAD [Source Client] as Client, PartnerID, [Sale Amt] RESIDENT Source WHERE NOT EXISTS(Client, [Source Client]);
[Notes]: LOAD [Source Client] as Client, concat(Note,';') as Notes RESIDENT Source GROUP BY [Source Client];
