Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm not familiar on making this, but I saw a possibility on this... Here's the scenario:
I have this table structure:
| UserId | ProjectName | Region | Allocation | WeekNumber |
| 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!
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
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!
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
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.
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