Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
mrjohn23
Creator III
Creator III

Table Manipulation?

   

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?

1 Solution

Accepted Solutions
qlikrajan
Creator III
Creator III

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;


View solution in original post

4 Replies
chris_deniziak
Creator
Creator

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

qlikrajan
Creator III
Creator III

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;

mrjohn23
Creator III
Creator III
Author

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;

qlikrajan
Creator III
Creator III

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;


View solution in original post