topic Re: Turn Rows into Columns in QlikView

Turn Rows into Columns

ksomosera10 — Wed, 06 Sep 2017 07:39:11 GMT

Hi,

I'm not familiar on making this, but I saw a possibility on this... Here's the scenario:

I have this table structure:

| 1 | Project 1 | US | 50 | 34 |

| 2 | Project 7 | JP | 80 | 34 |

| 3 | Project 10 | US | 100 | 35 |

And I want it to be like this:

| UserId | Week 34 | Week 35 |

| 1 | 50 | 0 |

| 2 | 80 | 0 |

| 3 | 0 | 100 |

I need to turn the WeekNumber on rows into column header with Sum of Allocation as it's values. I don't need to use the other Columns just the UserId to identify what they accumulate each week.

I have this script if ever you want to know what is my current script:

LOAD [UserID]

,[ProjectName]

,[Allocation]

,[WeekNumber]

,[Region];

SQL EXEC dbo.usp_Qlik_Tracker;

Thanks in advance for your help!

Re: Turn Rows into Columns

effinty2112 — Wed, 06 Sep 2017 08:02:02 GMT

Hi Kevin,

You could try:

Data:

Generic

Load

UserId,

'Week ' & WeekNumber as WeekNumber,

Allocation;

LOAD * Inline [

UserId | ProjectName | Region | Allocation | WeekNumber

1 | Project 1 | US | 50 | 34

2 | Project 7 | JP | 80 | 34

3 | Project 10 | US | 100 | 35

] (delimiter is '|');

You get a data model like this:

You could join the tables together but you don't need to. As it is you get this tablebox

UserId	Week 34	Week 35
1	50
2	80
3		100

cheers

Andrew

Re: Turn Rows into Columns

ksomosera10 — Wed, 06 Sep 2017 08:07:39 GMT

Hi Andrew,

Thanks for responding. I just don't get in the "INLINE" part.

I have 52-53 possible weekNumber... Are you sayin' I should type the whole table inside "INLINE"? (Because I saw you did that by using the '|' as delimiter) Sorry, I'm not familiar with that INLINE syntax.

Thanks!

Re: Turn Rows into Columns

effinty2112 — Wed, 06 Sep 2017 08:14:19 GMT

Hi Kevin,

The inline part was just how I got your data into my model, you don't need that. The script I gave is a preceding load, the bottom half, the inline load, should be replaced in your script by script that loads from your source.

Something like:

Data:

Generic

Load

UserId,

'Week ' & WeekNumber as WeekNumber,

Allocation;

LOAD [UserID]

,[Allocation]

,[WeekNumber];

SQL EXEC dbo.usp_Qlik_Tracker;

Cheers

Andrew

Re: Turn Rows into Columns

ksomosera10 — Wed, 06 Sep 2017 08:23:43 GMT

Hi Andrew,

Just an inquiry, I'm reading your script.

does this two highlighted fields are different? or the same? Apologies, I'm New on "Generic Load" script.

Re: Turn Rows into Columns

effinty2112 — Wed, 06 Sep 2017 08:31:13 GMT

Hi Kevin:

A correction:

Data:

Generic

Load

UserID,

'Week ' & WeekNumber as WeekNumber,

Allocation;

LOAD [UserID]

,[Allocation]

,[WeekNumber];

SQL EXEC dbo.usp_Qlik_Tracker;

UserId changed to UserID.

Don't worry about being new on Generic Load, it's not used very often but it's good to know of for the odd occasion it comes in handy. I always think of it as the reverse of a CrossTable load.

Cheers

Andrew