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

[shansundar]

You can check whether this query also. Its working for me for the sample data. Using the result set generated by this query you can create your desired chart.

temp:

CrossTable(Dim, value, 3)

LOAD Company,

    YEAR,

    MONTH,

    LTI,

    UAC,

    OS,

    NM,

    FI

FROM

//change this to your lib path and table name.

(ooxml, embedded labels, table is Sheet1);

final:

NoConcatenate

LOAD

Company,

'Q' & ceil(month(Date)/3) as Quarter_number,

Dim,

value;

LOAD

Company,

Date(Date#(YEAR&MONTH, 'YYYYMMMM')) as Date,

Dim,

value

RESIDENT temp;

DROP TABLE temp;

Thanks,

Shan S

[akpofureenughwu]

Hello Subbiah,

I confirmed that I modified the script and the lib path...

It's working now Thanks

[akpofureenughwu]

stalwar1    I have modified the lib path .. Thanks to shansundar‌

This is the existing script

LIB CONNECT TO 'HSE';

[GAB]:

CrossTable (Dim, Value, 6)

LOAD ASSET ,

    "FIELD" ,

    "YEAR",

    "MONTH" ,

    Date(Date#(YEAR&MONTH, 'YYYYMMMM')) as Date,

    FAT,

    LTI,

    RTA,

    UA,

    UC,

    NM,

    OS,

    "DO",

    CD,

    MTC;

SQL SELECT ASSET,

    "FIELD",

    "YEAR",

    "MONTH",

    FAT,

    LTI,

    RTA,

    UA,

    UC,

    NM,

    OS,

    "DO",

    CD,

    MTC

FROM "HSE".dbo."INCIDENTS REPORT HSE";

FinalTable:

NoConcatenate

LOAD ASSET as Asset,

    "FIELD" as Field,

    "YEAR"as Year,

    "MONTH" as Month,

    Date,

    Value

Resident GAB ;

DROP Table GAB;

But my challenge is the values of LTI, FAT and others are combined and not distinct.... See attached (value1)

can't the design  in HSI still achievable?

Regards