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

SQL SERVER table design for easier QV manipulation

Before I experiment, I wish to use some experience from those who have come before me! thank you in advance.

I have sales and revenue data in 5 dimensions in my SQLSERVER (SS) data tables.

FiscalPeriod -> 201301,201302, etc

CustomerNbr,

Product -> FA,SD, EX, SP, etc

OrderType ->  Booking (Sales), Execution (Revenue)

RevenueType -> Install, FP, Amortized, Material Only, Labor only, Labor and Material

Value in Dollars

Which SS staging table will be easier to set up graphs and dashboards after importing into QV:

Table 1:  (DB Normalized)

FiscalPeriod,

CustomerNbr,

Product

OrderType,

RevenueType,

Value

Table 2: (DB - Not Normalized)

FiscalPeriod

Customer Number

Product

OrderType,

RevType1Value,

RevType2Value,

RevType3Value,

RevType4Value,

RevType5Value,

TotalRevValue

Thanks in advance.

Sportsgirl. . .

1 Solution

Accepted Solutions
adamwilson
Partner - Creator
Partner - Creator

if you use Count( DISTINCT CustomerNbr) it will be unique for each revenue type

View solution in original post

5 Replies
adamwilson
Partner - Creator
Partner - Creator

Table 1 would be my preference so I can use RevenueType as a ListBox or Dimension.

Carlos_Reyes
Partner - Specialist
Partner - Specialist

Table 1 is better for me too.

Not applicable
Author

So then if there is a customer count for all revenue types,

will the customer be unique customer count? or counted twice, once for each revenue type?

Customer counts will be very important for an average per customer of the misture of different revenue types. .

just curious before doing the SQLSERVER setup and testing. . .

thanks

T L

adamwilson
Partner - Creator
Partner - Creator

if you use Count( DISTINCT CustomerNbr) it will be unique for each revenue type

Not applicable
Author

Count( DISTINCT CustomerNbr) exists within SQL SERVER, but not within MS Access, which shows I came up through the desktop database world. . .  Ace thank you again!