Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
akpofureenughwu
Creator III
Creator III

How to interswitch dimension and measure

Hello there,

I have a set of data in this format.

CompanyYEARMONTHLTIUACOSNMFI
JohnLee2016January1561243
BarryHouses2016October04044
Choi and Sons2016December1340451

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

12 Replies
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
MVP & Luminary
MVP & Luminary

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Partner
Partner

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