Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Crosstable load with different data types

Hello all. I have a question regarding how to approach this load. I have a CSV that has information for 2015 and 2016.  I am unable to make changes to and have approached this using a cross table load. The problem is the data for the crosstable has two different data type similar to the structure found below.

IDLocationPracticeRateJan 2016Feb 2016Mar 2016Jan 2016Feb 2016Mar 2016
1NAFinance$2001510$30$90150$
2NASales$400200$0$180090$

Now I have tried using crosstables to make (Months, Hours) and use a master calender to populate missing dates but I am unable to use the data values it correctly as the currency value gets factored in along with the hours so each month has a combined data value. The second set of 2016 dates has currency data that is ( Rate x Hour ) so would it be possible to load all the hours and calculate to populate a table after ?

If it makes it easier, I wish to have my data in this type of manner to create visualizations off as I think this would make it easiest to work with compared to the form it is now.

IDLocationPractice RateMonthHoursRevenue
1NAFinance$200Jan 20161$30
1NAFinance$200Feb 20165$90
1NAFinance$200Mar 201610$150
2NASales$40Jan 20160$0
4 Replies
MVP
MVP

Re: Crosstable load with different data types

May be like this:

Hour:

CrossTable(MonthYear, Hours, 4)

LOAD ID,

    Location,

    Practice,

    Rate,

    [Jan 2016],

    [Feb 2016],

    [Mar 2016]

FROM

[https://community.qlik.com/thread/239161]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

NoConcatenate

LOAD ID,

  Location,

  Practice,

  Rate,

  Date(MonthStart(Date#(MonthYear, 'MMM YYYY')), 'MMM YYYY') as MonthYear,

  Hours

Resident Hour;

DROP Table Hour;

Revenue:

CrossTable(MonthYear, Revenue, 4)

LOAD ID,

    Location,

    Practice,

    Rate,

    [Jan 20161],

    [Feb 20161],

    [Mar 20161]

FROM

[https://community.qlik.com/thread/239161]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (FinalTable)

LOAD ID,

  Location,

  Practice,

  Rate,

  Date(MonthStart(Date#(Left(MonthYear, 8), 'MMM YYYY')), 'MMM YYYY') as MonthYear,

  Money(PurgeChar(Revenue, '$'), '$#,##0') as Revenue

Resident Revenue;

DROP Table Revenue;

Capture.PNG

Not applicable

Re: Crosstable load with different data types

Thank you for your response Sunny! I tried using the syntax you described but was unable to get the revenue data to display properly. When using the dimension in visualizations it is representative of 0. Attached is the qlik sense file I've been working on so far, it might provide the community with a better idea of my app. Any help is appreciated while I continue to work on this problem, thanks.

MVP
MVP

Re: Crosstable load with different data types

Can you share the Excel file you used to upload it into the application?

Not applicable

Re: Crosstable load with different data types

Yes here is the excel file, excluding sensitive information.

Community Browser