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

Data Architecture

HI All

I am having a little trouble trying to work out the best architecture for a document.

I am looking at Reporter Productivity. I have:

  • a main table that has a field 'first reporter' and another 'second reporter'. Productivity takes into account both these fields
  • another table with the Reporter Details in it including the expected amount that should have been reported

Basically what i would like to do is have a table that has: The reporter name and then a count of how many times they appear in the first reporter column and another count of how many times that appear in the second reporter column, A total and also the expected.

Please see the attached example. Note i have not joined the tables yet as i am unsure on the best place to do this in this situation.

Any help would be great.

Thanks

Dan

1 Solution

Accepted Solutions
Or
MVP
MVP

JOIN in this case is superfluous - since the two tables have identical fields, they will be joined implicitly. I tend to include the JOIN for ease of reading.

For the difference between JOIN and CONCATENATE, see http://qlikviewnotes.blogspot.com/2009/11/understanding-join-and-concatenate.html

View solution in original post

10 Replies
Or
MVP
MVP

What I would do in this instance is load the "fact" table twice. In pseudocode:

Load ActivityID, FirstReporter as Reporter, "First Reporter" as ReporterType

From ReportsCompleted;

JOIN

Load ActivityID, SecondReport as Reporter, "Second Reporter" as ReporterType

From ReportsCompleted;

Load *

From ReporterDetails;

Not applicable
Author

Thanks for the reply. This will work but is there any other options? I am loading about 1million records and growing each month so i would prefer not to load it twice if i can avoid it.

Thanks

Dan

Not applicable
Author

Thanks for the reply. This will work but is there any other options? I am loading about 1million records and growing each month so i would prefer not to load it twice if i can avoid it.

Thanks

Dan

Or
MVP
MVP

I suppose you could create a complex key based on both reporters, and set up a table that includes every possible permutation to join it with.. but I would not go that way.

Is there a reason not to load twice? 1m records is really not a lot - I have models with 10m+ that are loading on an hourly basis with some tables being read 3-5 times. If you're worried about I/O, you can just load the table from file once and then use resident loads - this should go very quickly, especially if your records are composed of just a small handful of columns each. Because of the way QV stores data, using a double-load should not significantly increase the storage space / RAM required, either.

TemporaryLoad:

Load * from ReportsCompleted;

Reports:

Load ActivityID, FirstReporter as Reporter, "First Reporter" as ReporterType

Resident TemporaryLoad;

JOIN

Load ActivityID, SecondReport as Reporter, "Second Reporter" as ReporterType

Resident TemporaryLoad;

drop table TemporaryLoad;

Reporters:

Load *

From ReporterDetails;

Not applicable
Author

HI

Thnaks again for the reply and the explanation of why it is not a problem to load the table multiple times. I really appreaciate it.

One last question if you have time. I read somewhere that the Map Function and Join are the same. Is this correct? I thought the map function would map an extra field to a table. I guess join could be used to either join two tables toegether like CONCATANATE but also to add an extra field?

kind regards.

Dan

Not applicable
Author

HI

Thnaks again for the reply and the explanation of why it is not a problem to load the table multiple times. I really appreaciate it.

One last question if you have time. I read somewhere that the Map Function and Join are the same. Is this correct? I thought the map function would map an extra field to a table. I guess join could be used to either join two tables toegether like CONCATANATE but also to add an extra field?

kind regards.

Dan

Or
MVP
MVP

JOIN forces two tables (LOAD results) together. Map allows you to grab a single value from a pre-loaded Mapping Load table, which is then discarded. I rarely use mapping loads, and when I do, it's always because they make the code neater, never for any other reason. Perhaps other forumites can provide some examples of when it's best to use each type, but this isn't really my field.

Not applicable
Author

thanks again. So can i please clarify: Join (without specifying a type of join) will combine the two tables together. This sounds the same as Concatanate? Am I correct in thinking this or are there pros and cons of each,

regards,

DAn

Or
MVP
MVP

JOIN in this case is superfluous - since the two tables have identical fields, they will be joined implicitly. I tend to include the JOIN for ease of reading.

For the difference between JOIN and CONCATENATE, see http://qlikviewnotes.blogspot.com/2009/11/understanding-join-and-concatenate.html