Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to summarise records during load?

Hello,

I may be trying to approach this in completely the wrong way, but i have the following scenario that i could do with some advice on:

I have one table that i am loading which contains a list of Calls e.g.:

Calls:

ID, Open Date, Close Date

Call1, 01/03/2010, 02/03/2010

Call2, 01/03/2010, 01/03/2010

etc...

I then have another table that I load which contains the same 1 or more rows for each call along with the 'Companies' that the Call Affected, this covers a total of 2 companies e.g.

ID, Location

Call1, Company2

Call1, Company1

Call2, Company1

etc....

What i need to produce is a summary showing the number of Calls that affected

1. Just Company1

2. Just Company2

3. Both Companies

I have done similar analysis in excel and i do this by reading in a VBA macro each Company value for each row related to each call, and creating a Summary value of Comany1, Company2 or Both, i then store this value along with the CallID in a seperate table so i have a single row of data for each call, which i can then analyse: e.g.

Summary Table:

ID, Open Date, Close Date, AffectedCompanySummary

Call1, 01/03/2010, 02/03/2010, Both

Call2, 01/03/2010, 01/03/2010, Company1

Can i do something during my load into Qlikview, I dont want to lose the granularity of the data though, or is there a smarter way of doing this?

Can someone point me in the right direction?

Many thanks!!

3 Replies
prieper
Master II
Master II

Solution might be depending a bit on the real data, assuming that the second table has for each call max one entry for either company1 or company2 (or both), you may work with multiples of 2: assign value 1 to company1, value 2 to company2 (value 4 to company3) and then sum up the values. If result is 1, you have only company1. If result is 2, there is only company2, if 3 they are mixed.

HTH
Peter

Not applicable
Author

Thanks Peter, but i've proabably oversimplified things above. I pull the info from a SQL database, and there can be up to 50 diffrent rows in the second table relating to various departments of companies 1 & 2.

Is there any way of reading the lines of data as i bring them in, in some kind of loop FOR ... NEXT... etc... to create rows in a new 'summary' table?

I have other similar situations where i can see this requirement as well.

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi there,

If you can be sure that there will only ever be two companies in the second table you could use a couple of mapping loads onto the main call table. It would be something like this:

Map_Company1Calls:
MAPPING LOAD
ID,
1 as CallCount
FROM CallCompanies
WHERE Location = 'Company1';

[Similar mapping load for Company 2 Calls]

Calls:
LOAD
ID,
[Open Date],
[Close Date],
ApplyMap('Map_Company1Calls', ID, 0) as Company1Count,
ApplyMap('Map_Company2Calls', ID, 0) as Company2Count
FROM Calls;

Once you've done that you've two fields you can sum for Company 1 Count and Company 2 Count. You can then either use logic on those two flags to work out how many calls are for both companies, or you could put the following preceding load on the calls table:

Calls:
LOAD
*,
if(Company1Count + Company2Count > 1, 1, 0) as BothCompaniesCount,
if(Company1Count + Company2Count = 0, 1, 0) as NeitherCompaniesCount;
LOAD
[etc...]

Hope that all makes some kind of sense. Post back here if you need any further pointers.

Regards,
Steve