Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
akpofureenughwu
Contributor II

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

Re: How to interswitch dimension and measure

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

Re: How to interswitch dimension and measure

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
Contributor II

Re: How to interswitch dimension and measure

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

Re: How to interswitch dimension and measure

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
Contributor II

Re: How to interswitch dimension and measure

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

Re: How to interswitch dimension and measure

What error message do you get?

akpofureenughwu
Contributor II

Re: How to interswitch dimension and measure

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

Re: How to interswitch dimension and measure

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

shansundar
Contributor

Re: How to interswitch dimension and measure

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

Community Browser