How to interswitch dimension and measure

[akpofureenughwu]

Hello there,

I have a set of data in this format.

Company	YEAR	MONTH	LTI	UAC	OS	NM	FI
JohnLee	2016	January	1	56	12	4	3
BarryHouses	2016	October	0	4	0	4	4
Choi and Sons	2016	December	1	34	0	45	1

after the loading the data into my qliksense app, the data looks like this:

LOAD *,

if( Isnum(Date), 'Q')& Ceil(Month(Date)/3) as Quarter;

LOAD *,

    Date(Date#(Year&Month, 'YYYYMMMM')) as Date;

LOAD Company

        "YEAR"as Year,

    "MONTH" as Month,

    FAT,

    LTI,

    RTA,

    UA,

    UC,

    NM,

XXXXXXXXXX

With this, I can easily build a visualization, using Company, Year, Quarter  and Month  as dimension and LTI, UAC, OS as measures.(picture 1)

The clients wants the reverse (LTI, UAC, OS as dimension and Quarter measure) (picture HSI)

See attached for clarification

[sunny_talwar]

I would use The Crosstable Load to transform your data and then you should be able to create visuals of your desire

[marcus_sommer]

I think I would transform the data with The Crosstable Load and than map or associate in a table the descriptions to the codes of LTI and son on.

- Marcus

[akpofureenughwu]

Hello sunny,, Thanks for this assistance,

I have used this crosstable features for one value system (more like date)... How do I use it for more than one value like this case

LIB CONNECT TO 'HSE';

[HSE]:

CrossTable (LTI, FAT, Stats, 2)

Load LTI,

  ASSET as Asset,

    "FIELD" as Field,

    "YEAR"as Year,

    "MONTH" as Month,

      Date(Date#(Year&Month, 'YYYYMMMM')) as Date

FROM "NPDC HSE".dbo."INCIDENTS REPORT HSE"  

FinalTable:

NoConcatenate

LOAD

LOAD ASSET as Asset,

    "FIELD" as Field,

    "YEAR"as Year,

    "MONTH" as Month,

    Date,

    Stats

Resident HSE;

DROP Table HSE;

I tried this and I got an error message..

Regards

[sunny_talwar]

The CrossTable will look like something like this

[HSE]:

CrossTable (Dim, Value, 6)

Load LTI,

  ASSET as Asset,

    "FIELD" as Field,

    "YEAR"as Year,

    "MONTH" as Month,

      Date(Date#(Year&Month, 'YYYYMMMM')) as Date,

FAT,

LTI,

RTA,

UA,

UC,

NM,

OS,

DO,

CD,

MTC

FROM "NPDC HSE".dbo."INCIDENTS REPORT HSE" 

[akpofureenughwu]

A quick one Sunny,

The dimension can I combine all ten dimensions at one  or load one at a time?

Cos when I loaded your prescribed format, I got an error message

[sunny_talwar]

What error message do you get?

[akpofureenughwu]

I used this script:

LIB CONNECT TO 'HSE';

[HSE]:

CrossTable (Dim, Value, 6)

Load LTI,

  ASSET as Asset,

    "FIELD" as Field,

    "YEAR"as Year,

    "MONTH" as Month,

      Date(Date#(Year&Month, 'YYYYMMMM')) as Date,

FAT,

LTI,

RTA,

UA,

UC,

NM,

OS,

DO,

CD,

MTC

FROM "NPDC HSE".dbo."INCIDENTS REPORT HSE"

FinalTable:

NoConcatenate

LOAD

LOAD ASSET as Asset,

    "FIELD" as Field,

    "YEAR"as Year,

    "MONTH" as Month,

    Date,

    Value

Resident HSE;

DROP Table HSE;

I got an error ... see attached

[sunny_talwar]

Have never seen this error before, somebody else might be able to help better here

[shansundar]

Check if your Lib path is created in this workbook or not.