Table Manipulation?

[Anonymous]

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?

[Anonymous]

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 *****

[Anonymous]

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;

[Anonymous]

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;

[Anonymous · Accepted solution]

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;