Display dimension data in multiple columns

[former_member200750]

Hi Guys,

In the Universe I have a table with the following dimensions UserID, Device Type, Time (see the sample data below). A user could have worked with a multiple devices over the period but can have only one at a time.

I need to create a report to find out whether the each user(user_id) had used a certain device type or not. If yes display the device type otherwise blank. The problem here is I have to display them in separate columns based on the device type. So basically in this case I need to display a column for each device type based for each user ID (something like this).

Not sure how this can be achieved. Any help would be appreciated.

Thanks

Dilip

[Former Member · Accepted solution]

Hi Dilip,

Pranay's first answer should have worked. He has suggested to create report level variables and not where clauses in the query panel.

Kind Regards

Mohan

[former_member200750 · Accepted solution]

Thanks Mohan. Sorry I misread Pranay's first post but even then in the report it didn't work. I have already tried that in the report and it returned me something like this (see below img). I wanted only one single row for each user_id.

[Former Member · Accepted solution]

Hi Dilip,

You need to tweak your variable definition to include the context

PC_Device =[Device Name] Where[Device Name]='PC_Device' in ([User Id])

Mac_Device=[Device Name] Where[Device Name]='Mac_Device' in ([User Id])

Cheers

Mohan

[former_member200750 · Accepted solution]

I did the same thing after posting my response to your initial reply and it worked. Thanks for all your help.

[former_member203850]

You can create two different variables in report for the above requirement. For example:

PC_Device =[Device Name] Where[Device Name]='PC_Device'

Mac_Device=[Device Name] Where[Device Name]='Mac_Device'

And pull both  variables in a report. Please check and let me know how it goes.

Regards,

Pranay

[former_member200750]

Thanks for your response Pranay. Sorry, I forgot to mention it in my original post that I have already tried the above scenario you mentioned. There was no data coming through when I pulled the UserID, PC_Device and Mac_Device columns in to the query. I checked the generated SQL and realized that it was generating an 'AND' in the where clause (see the below sql) so I changed the SQL(to use custom query) to 'OR' instead of 'AND" in the where clause, it was returning the data but its repeating the records twice (see the below table) which is not what I wanted .  My requirement is to get single row for each user_id with PC_device column displaying 'PC' and Mac_Device column displaying 'Mac'.

SELECT

  Table__2."user_id",

  Table__2."device_type",

  Table__2."device_type"

FROM

  Table__2

WHERE

( Table__2."device_type"='PC' )

AND ( Table__2."device_type"='Mac')

So far I'm trying this in IDT 4.1sp3, not sure if there is a way we can create some variables and solve this issue in the Webi.

[former_member203850]

Try below approach:

Query 1 : For PC

Query 2 Only for MAC

And apply union between Query 1 and query 2 in adhoc query section.

Then create variables for PC and MAC in report and pull both objects in a report.

Please check and and let me know how it goes.

Regards,

Pranay

[former_member200750]

The Union of the queries didn't work. It gave me repeated rows for each user id. However a tweak to your first solution (including the context to the variable) worked. Thanks for your help.