topic Re: Table Manipulation? in QlikView

Table Manipulation?

Anonymous — Thu, 19 Feb 2015 20:09:53 GMT

Hello All,

I have a table, see below.

PersonID

ID KeyID PersonID PersonIDRank

1 123 4444 1

1 123 4321 2

2 124 4211 1

What I would like to see is:

ID KeyID PersonIDRank1 PersonIDRank2

1 123 4444 4321

2 124 4211 Null

There are unlimited ranks and many records but I only need to see up to 3 Ranks in a column

Any thoughts on how I can do this?

Re: Table Manipulation?

Anonymous — Thu, 19 Feb 2015 20:22:46 GMT

Add a preceding load to your existing load statement to look at ranks. Something like this:

LOAD

ID,

KeyID,

if(Rank = 1, PersonID) as PersonIDRank1,

if(Rank = 2, PersonID) as PersonIDRank2,

if(Rank = 3, PersonID) as PersonIDRank3

;

LOAD

***** Existing load here *****

Re: Table Manipulation?

Anonymous — Thu, 19 Feb 2015 22:33:18 GMT

Please see the attached app. I have done the manipulation in script. I have added a few more data points to make the result noticeable. Let me know if you find any issue.

here is the code:

Data:

LOAD *,

If(Rank=1,PersonID) as Rank1,

If(Rank=2,PersonID) as Rank2,

If(Rank=3,PersonID) as Rank3

Where Match(Rank,1,2,3);

LOAD ID,

KeyID,

PersonID,

Rank

FROM

Sample.xlsx

(ooxml, embedded labels, table is Sheet6);

NoConcatenate

LOAD ID,

KeyID,

max(Rank1)as PersonIDRank1,

max(Rank2)as PersonIDRank2,

max(Rank3)as PersonIDRank3

Group By ID, KeyID;

Data1:

LOAD *

Resident Data;

Drop Table Data;

Re: Table Manipulation?

Anonymous — Fri, 20 Feb 2015 13:47:17 GMT

This was very helpful but of course I cannot get the query to run. I should have posted my sntax in the question. So the below is extracting for SQL, how would I then add your above query to the syntax. My apologies for not being clear.

sql

SELECT
ID,
KeyID,
personID,
RANK() OVER(PARTITION BY ID, KeyID ORDER BY ID, KeyID, PersonID as PersonID_Rank

FROM nm WITH(NOLOCK)
JOIN aj WITH(NOLOCK) ON aj.KeyID = nm.KeyID

WHERE ID IS NOT NULL and ID <> '' AND ID <> 0

GROUP BY ID, nm.PersonID, aj.KeyID;

Re: Table Manipulation?

Anonymous — Fri, 20 Feb 2015 14:42:59 GMT

Hey John:

Glad that you found it helpful. Once you extract the data from SQL then write a Load statement on the top of that. This will bring all the records in the inbuilt memory of Qlikview. From there you can start manipulating data using qlikview functions. Code:

Data:

LOAD *, //2nd preceding load for manipulation in qlikview

If(PersonID_Rank=1,PersonID) as Rank1,

If(PersonID_Rank=2,PersonID) as Rank2,

If(PersonID_Rank=3,PersonID) as Rank3

Where Match(Rank,1,2,3);

Load *; // 1st Preceding load to bring data in qlikview in built memory

sql

SELECT
ID,
KeyID,
personID,
RANK() OVER(PARTITION BY ID, KeyID ORDER BY ID, KeyID, PersonID as PersonID_Rank

FROM nm WITH(NOLOCK)
JOIN aj WITH(NOLOCK) ON aj.KeyID = nm.KeyID

WHERE ID IS NOT NULL and ID <> '' AND ID <> 0

GROUP BY ID, nm.PersonID, aj.KeyID;

NoConcatenate

LOAD ID,

KeyID,

max(Rank1)as PersonIDRank1,

max(Rank2)as PersonIDRank2,

max(Rank3)as PersonIDRank3

Group By ID, KeyID;

Data1:

LOAD *

Resident Data;

Drop Table Data;