topic Re: turn row value in to column in QlikView

turn row value in to column

anatle1234 — Wed, 25 Nov 2020 16:16:04 GMT

hi,

I have this table in data base:

order_id site_id ........

1 2

1 3

I have more columns in this table but what I need is for this specific values (site id and order id) to make it look like this in the table in QV:

order_id Site A Site Z

1 2 3

Instead of 2 rows I want to see it in columns but without affecting all other columns in the report..

can someone help?

thanks

Anat

Re: turn row value in to column

Gysbert_Wassenaar — Wed, 20 Dec 2017 09:13:18 GMT

Perhaps this helps: The Generic Load

Re: turn row value in to column

Anil_Babu_Samineni — Wed, 20 Dec 2017 09:14:19 GMT

Generic Load Fields,,,...

Re: turn row value in to column

anatle1234 — Wed, 20 Dec 2017 09:22:15 GMT

but the generic load example is when 3 columns are involved:

but I don't have the third column I have 2.

Re: turn row value in to column

Or — Wed, 20 Dec 2017 09:28:45 GMT

Perhaps someone else will have a simpler approach, but this seems like a case where I'd use row numbering by dimensions:

Orders:

Load * INLINE [

OrderID, SiteID

1, 2

1, 3

2, 2

3, 1

3, 3

3, 4

];

Left join (Orders)

Load OrderID, SiteID, if(OrderID=previous(OrderID),rangesum(1,peek(Row)),1) as Row, 'Site ' & if(OrderID=previous(OrderID),rangesum(1,peek(Row)),1) as RowDisplay

Resident Orders

Order By OrderID, SiteID;

The resulting chart (pivoted by RowDisplay):

The concept is further elaborated on here:

Count rows per group

Re: turn row value in to column

Gysbert_Wassenaar — Wed, 20 Dec 2017 09:29:33 GMT

Then how do you know that site_id 2 translates to Site A? Apparently you have that information somewhere or can generate it using some logic.

Re: turn row value in to column

anatle1234 — Wed, 20 Dec 2017 09:31:34 GMT

thanks

this seems like what I need, I will try it.

Re: turn row value in to column

Or — Wed, 20 Dec 2017 10:03:54 GMT

That got me at first too, but I think they aren't actually SiteA, SiteB, etc. They're just generic "First site, second site, third site".

Re: turn row value in to column

anatle1234 — Wed, 20 Dec 2017 11:25:06 GMT

I am not able to get SITE 1, SITE2 as column, I get it in rows. can you please explain what you put in the dimension and expression?

Re: turn row value in to column

Or — Wed, 20 Dec 2017 11:43:36 GMT

I created a pivot table and set the Row as the pivoted dimension (by dragging it to the top of the pivot table).

Re: turn row value in to column

anatle1234 — Wed, 20 Dec 2017 11:57:10 GMT

I am using your code but I am not able to get table like you put in your comment. can you please explain in more details what exactly you have in dimension and expression? sorry I am new to QV. I tried to put Row in dimension but it didn't work

Re: turn row value in to column

anatle1234 — Wed, 20 Dec 2017 12:30:39 GMT

I am not able to drag the blue sign is always vertical. what did you put in expression? you have orderID and RawDidsplay as dimensions? how did you drag it

Re: turn row value in to column

anatle1234 — Wed, 20 Dec 2017 13:04:12 GMT

never mind it worked. thanks

Re: turn row value in to column

anatle1234 — Wed, 20 Dec 2017 13:44:14 GMT

another small question.......is there a way to rename Site 1, Site 2 to Site A , Site B? I will always have 2 sites only

Re: turn row value in to column

Or — Wed, 20 Dec 2017 14:16:04 GMT

If it's just two sites, this entire solution was probably a little more elaborate than necessary - the assumption was that it would be a large number (since you used A and Z)...

Something along the lines of:

if(OrderID=previous(OrderID),'Site Z', 'Site A') as SiteForDisplay