Switching dimensions from X to Y axis in straight table or table box (and not pivot table)

[stevelord]

Hi, I am trying to get my dimensions over to the left end of the rows instead of the column headers in a straight table or table box.  They are not related in such a way for a pivot table to make any sense.  I just want them listed together in one table so I can use them like a selection box.

Currently the data comes out like this in  the table box or straight table:

BMI                         Body Fat %

Normal   (<=4.9)       -

High (>4.9)               -

-                              Low (<10)

-                              Normal (10-20.9)

-                              High (>20.9)

What I want is:

BMI                 Normal (<=4.9)

BMI                 High (>4.9)

Body Fat %     Low (<10)

Body Fat %     Normal (10-20.9)

Body Fat %     High (>20.9)

...

Pivot table blows up in my face by putting all values of the next column next to each value of the previous column, and the values aren't directly related anyway.  And I have 16 metrics; I'd like to do this without making 16 list boxes.   (Containers are aesthetically ugly too btw, but I'll experiment there as a workaround in the interim.)

Thanks!

-Steve

[#2]

bmi:

LOAD * INLINE [

    State,text,score

    BMI,normal, (<4.9)

    BMI,high, (>4.9)

];

bodyfat:

LOAD * INLINE [

    State,text, score

    BODYFAT,low, 10

    BODYFAT,normal, 10-20.9

    BODYFAT,high, >20.9

];

then it would look like this:

But try to post how you load your tables, then i can see what you should do! - Is it 1 table you load or 2 tables?

[stevelord]

I can duplicate your result above but can't get it to operate my tables.  Below is the wonderful script I have with lots of table kung fu.  Most fields are built from computations run on values within one or a few other fields. (Note: no personally identifiable info is in this script and QV community helped a bit on it, so I'm fine dropping the complete script in here!)

Note: I also have one set of list boxes and charts under Aggregate State and one group of charts and listboxes under Client State objects on the worksheets themselves.

Directory;
LOAD ClientAccountID,
ClientAccountName,
     if(wildMatch(ClientAccountName, 'test*', 'grtest'), 'TestClient', 'Client') as [Test Clients]
// See source document for other fields available
FROM
[ClientAccounts.qvd]
(qvd);

Directory;
LOAD OfficeID,
     ClientAccountID
// See source document for other fields available
FROM
[Offices.qvd]
(qvd);

Directory;
LOAD TEXT(UserId) as UserId,
     UserEmail,
     if(wildMatch(ExternalID, 'lht*', 'd_lh*', 'lh_def*',
'grctest*', '*_TEST*', '*TEST_*'), 'TestUser', 'User') as [Test Cases],
     LastName,
     FirstName,
     TEXT(ExternalID) as ExternalID,
     OfficeID,
     MemberStatus
// See source document for other fields available
FROM
[Users.qvd]
(qvd);

tempBiometrics:
LOAD TEXT(UserId) as UserId,
Units,
   Source,
        TEXT(UserId) & '|' & Units & '|' & Date(Floor(TestDate))  as %Key, 
TestName,
        TestValue,
if(TestName='BMI' AND TestValue>0 AND TestValue<18.6, Dual('At Risk, Underweight (<18.6)',1),
    if(TestName='BMI' AND TestValue>=18.6 AND TestValue<=24.9, Dual('Normal Weight (18.6-24.9)',2),
    if(TestName='BMI' AND TestValue>24.9 AND TestValue<500, Dual('At Risk, Overweight/Obese (>24.9)',3)))) as [BMI],
if(TestName='Waist Girth (Male)' AND TestValue>0 AND TestValue<40, Dual('Normal (<40)',1),
    if(TestName='Waist Girth (Male)' AND TestValue>=40 AND TestValue<500, Dual('At Risk, High (≥40)',2))) as [Waist Girth (Male)],
    if(TestName='Waist Girth (Female)' AND TestValue>0 AND TestValue<35, Dual('Normal (<35)',1),
    if(TestName='Waist Girth (Female)' AND TestValue>=35 AND TestValue<500, Dual('At Risk, High (≥35)',2))) as [Waist Girth (Female)],
if(TestName='Body Fat (Male)' AND TestValue>0 AND TestValue<10, Dual('At Risk, Low (<10)',1),
    if(TestName='Body Fat (Male)' AND TestValue>=10 AND TestValue<=20.9, Dual('Normal (10-20.9)',2),
    if(TestName='Body Fat (Male)' AND TestValue>20.9 AND TestValue<=100, Dual('At Risk, High (>20.9)',3)))) as [Body Fat (Male)],
    if(TestName='Body Fat (Female)' AND TestValue>0 AND TestValue<20, Dual('At Risk, Low (<20)',1),
    if(TestName='Body Fat (Female)' AND TestValue>=20 AND TestValue<=30.9, Dual('Normal (20-30.9)',2),
    if(TestName='Body Fat (Female)' AND TestValue>30.9 AND TestValue<=100, Dual('At Risk, High (>30.9)',3)))) as [Body Fat (Female)],
if(TestName='Total Cholesterol' AND TestValue>0 AND TestValue<200, Dual('Normal (<200)',1),
    if(TestName='Total Cholesterol' AND TestValue>=200 AND TestValue<7000, Dual('At Risk, High (≥200)',2))) as [Total Cholesterol],
    if(TestName='LDL' AND TestValue>0 AND TestValue<129, Dual('Normal (<129)',1),
    if(TestName='LDL' AND TestValue>=129 AND TestValue<1000, Dual('At Risk, High/Very High (≥129)',2))) as [LDL],
if(TestName='Triglycerides' AND TestValue>0 AND TestValue<150, Dual('Normal (<150)',1),
    if(TestName='Triglycerides' AND TestValue>=150 AND TestValue<5000, Dual('At Risk, High/Very High (≥150)',1))) as [Triglycerides],
    if(TestName='Fasting' AND TestValue>0 AND TestValue<100, Dual('Normal (<100)',2),
    if(TestName='Fasting' AND TestValue>=100 AND TestValue<3000, Dual('At Risk, High (≥100)',2))) as [Fasting],
if(TestName='NonFasting' AND TestValue>0 AND TestValue<=139, Dual('Normal (≤139)',1),
    if(TestName='NonFasting' AND TestValue>139 AND TestValue<3000, Dual('At Risk, High (>139)',2))) as [NonFasting],
if(TestName='Systolic' AND TestValue>0 AND TestValue<120, Dual('Normal (<120)',1),
    if(TestName='Systolic' AND TestValue>=120 AND  TestValue<1000, Dual('At Risk, High (≥120)',2))) as [Systolic],
if(TestName='Diastolic' AND TestValue>0 AND TestValue<80, Dual('Normal (<80)',1),
    if(TestName='Diastolic' AND TestValue>=80 AND TestValue<1000, Dual('At Risk, High (≥80)',2))) as [Diastolic],
if(TestName='HDL' AND TestValue>0 AND TestValue<40, Dual('At Risk-All (<40)',3),
    if(TestName='HDL' AND TestValue>=40 AND TestValue<50, Dual('At Risk-Female, Normal-Male (40-<50)',2),
    if(TestName='HDL' AND TestValue>=50 AND TestValue<1000, Dual('Normal-All (≥50)',1)))) as HDL,
     ImportedDate,
        Date(Floor(TestDate)) as TestDate,
     Year(TestDate) as Year,
   Month(TestDate) as Month
FROM
[Biometrics.qvd]
(qvd) WHERE(TestName='BMI'
OR TestName='Waist Girth (Male)'
OR TestName='Waist Girth (Female)'
OR TestName='Body Fat (Male)'
OR TestName='Body Fat (Female)'
OR TestName='Total Cholesterol'
OR TestName='HDL'
OR TestName='LDL'
OR TestName='Triglycerides'
OR TestName='Fasting'
OR TestName='NonFasting'
OR TestName='Systolic'
OR TestName='Diastolic')
AND Date(NOW())>=(TestDate) //filter junk dates (excludes test results dated in the future)
AND Year(TestDate)>2008 //filter junk dates (2009 earliest year in our records)
AND TestValue;

Left Join

UserAttributes:
LOAD TEXT(UserId) as UserId,
  AttributeName,
     AttributeValue,
     if(AttributeName='Gender' AND AttributeValue='', Dual('No Value',1),
  if(AttributeName='Gender' AND wildMatch(AttributeValue,'F*','f*','W*','w*','G*','g*'), Dual('Female',2),
     if(AttributeName='Gender' AND wildMatch(AttributeValue,'M*','m*','B*','b*'), Dual('Male',3)))) as [Gender]
FROM
[UserAttributes.qvd]
(qvd) WHERE AttributeName='Gender';

Biometrics:
LOAD *, if(Gender='Female' AND TestName='HDL' AND TestValue>0 AND TestValue<50, Dual('At Risk, Low (<50)',2),
    if(Gender='Female' AND TestName='HDL' AND TestValue>=50 AND TestValue<1000, Dual('Normal (≥50)',1))) as [HDL (Female)],
if(Gender='Male' AND TestName='HDL' AND TestValue>0 AND TestValue<40, Dual('At Risk, Low (<40)',2),
    if(Gender='Male' AND TestName='HDL' AND TestValue>=40 AND TestValue<1000, Dual('Normal (≥40)',1))) as [HDL (Male)]
Resident tempBiometrics;
Drop Table tempBiometrics;

HDL:
LOAD TEXT(UserId) & '|' & Units & '|' & Date(Floor(TestDate))  as %Key, 
     TEXT(UserId) as UserId,
     TestValue as CholRatioHDL, //Exported HDL data from HDL tab in this script to make HDL.xlsx table
  Units,
     Date(Floor(TestDate)) as TestDate
FROM
HDL.xlsx
(ooxml, embedded labels, table is HDL)
WHERE TestValue;

Left Join

TotalCholesterol:
LOAD TEXT(UserId) & '|' & Units & '|' & Date(Floor(TestDate))  as %Key, 
  TEXT(UserId) as UserId,
     TestValue as CholRatioTotal, //Exported Total Cholesterol data from Total Cholesterol tab in this script to make TotalCholesterol.xlsx table
  Units,
     Date(Floor(TestDate)) as TestDate
FROM
[Total Cholesterol.xlsx]
(ooxml, embedded labels, table is TotalCholesterol)
WHERE TestValue;

CholesterolRatio:
LOAD *, if(CholRatioTotal/CholRatioHDL>0 AND CholRatioTotal/CholRatioHDL<=4.9, Dual('Normal (≤4.9)',2),
if(CholRatioTotal/CholRatioHDL>4.9 AND CholRatioTotal/CholRatioHDL<70000, Dual('At Risk, High (>4.9)',3))) as [Cholesterol Ratio]
Resident HDL;
Drop table HDL;

Systolic:
LOAD TEXT(UserId) & '|' & Units & '|' & Date(Floor(TestDate))  as %Key, 
     TEXT(UserId) as UserId,
     TestValue as BPSystolic, //Exported Systolic data from Systolic tab in this script to make Systolic.xlsx table
  Units,
     Date(Floor(TestDate)) as TestDate
FROM
Systolic.xlsx
(ooxml, embedded labels, table is Systolic)
WHERE TestValue;

Left Join

Diastolic:
LOAD TEXT(UserId) & '|' & Units & '|' & Date(Floor(TestDate))  as %Key, 
  TEXT(UserId) as UserId,
     TestValue as BPDiastolic, //Exported Diastolic data from Diastolic tab in this script to make Diastolic.xlsx table
  Units,
     Date(Floor(TestDate)) as TestDate
FROM
Diastolic.xlsx
(ooxml, embedded labels, table is Diastolic)
WHERE TestValue;

SystolicDiastolic:
LOAD *, if((BPSystolic>0 AND BPSystolic<120) AND (BPDiastolic>0 AND BPDiastolic<80), Dual('Normal (<120/<80)',1),
if((BPSystolic>0 AND BPSystolic<120) AND (BPDiastolic>=80 AND BPDiastolic<2000), Dual('Sys Normal/Dia High (<120/≥80)',2),
if((BPSystolic>=120 AND BPSystolic<4000) AND (BPDiastolic>0 AND BPDiastolic<80), Dual('Dia Normal/Sys High (≥120/<80)',3),
if((BPSystolic>=120 AND BPSystolic<4000) AND (BPDiastolic>=80 AND BPDiastolic<2000), Dual('At Risk, High (≥120/≥80',4))))) as [SystolicDiastolic]
Resident Systolic;
Drop table Systolic;

LinkTable:
NoConcatenate load Distinct
       %Key,
       TEXT(UserId) as UserId,
       Units,
       Date(Floor(TestDate)) as TestDate      
Resident Biometrics;

Concatenate load Distinct
       %Key,
       TEXT(UserId) as UserId,
       Units,
       Date(Floor(TestDate)) as TestDate      
Resident CholesterolRatio;

Concatenate load Distinct
       %Key,
       TEXT(UserId) as UserId,
       Units,
       Date(Floor(TestDate)) as TestDate      
Resident SystolicDiastolic;

drop Fields UserId, Units, TestDate from Biometrics, CholesterolRatio, SystolicDiastolic; 

[#4]

could you maybe attach the doucment instead?

and holy shit a lot of scriptning!! Gotta love Data warehousing

And i thought best practice was to include your scriptning in your qvw doucment, and then make a qvd file... But maybe i guess one can do both..

[rwunderlich]

Can't say for sure without seeing your table model, but have you tried checking "horizontal" on the presentation tab?

-Rob

[stevelord]

Thanks, that was part of the solution (my subject line was an incomplete statement of my issue. ☺)

[stevelord]

I can’t attach the actual QV file because there is data among the results that I can’t share.

I can share the background script that I wrote with help from QV community though. (Notepad file attached)

Also attached is the table layout image. Image export was buggy but my screen only showed one of each table. The unlinked metrics tables in the lower right are some kind of glitche on the export.