Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pulling all unique values from a data set into a table

Hello,

I am attempting to pull all of the different values from a data set. For example, for each order number, I would like to see each time the date changed for that order. Is this possible?

5 Replies
Gysbert_Wassenaar

Create a Table object and add order number and date changed as dimensions. You'll get the unique combinations as rows in the Table.


talk is cheap, supply exceeds demand
quriouss
Creator III
Creator III

1.  Use a Table object

2.  Either drag the dimensions you want to see from the list on the left-hand-side onto the table (and when given the option, choose the thing you just dragged (probably you don't want to add it as a 'measure') or

     While the table object is selected, on the right-hand side there should be an option for 'Data', and under that 'Add Column'.  Choose Dimension, then add the dimension you want.

3.  If you want measures, e.g. invoice quantitiy, do the same thing, but choose Measures and the aggregation method if you want one.

Once the dimensions are added you can change the order by dragging and dropping from the *list of columns* on the right-hand-side of the screen, under 'data'.

Not applicable
Author

Hi,

I feel as though I probably did not explain my situation clearly… The order # is constant while the promise date changes. Is there a way to grab the different promise dates for each order number? For example, Order 123456 changed promise dates 4 times before it was delivered. Can I pull each one of those dates into a table?

Also, I am not seeing the Data option you described.

Thank you!

Not applicable
Author

As per my understanding, you have a table like below:

Order# Date_Modified

1         12-12-2014

1         12-12-2014

1         12-13-2014

1         12-14-2014

& You need convert those 3 rows into only one row like below:

Order# Date_1       Date_2       Date_3

1         12-12-2014  12-13-2014 12-14-2014

If YES, you can try like below:

Final:

LOAD

     Order# ,

     SubField( Date_list , '|' , 1) AS Date_1,

     SubField( Date_list , '|' , 2) AS Date_2,

     SubField( Date_list , '|' , 3) AS Date_3

;

LOAD

     Order# , Concat(Distinct Date_Modfied ,'|')  AS Date_List

From Table

Group By Order# ;

quriouss
Creator III
Creator III

Hi - apologies for the delay - my Qlik Community notifcations were going into my spam folder...

Anyway, did you get this fixed?  If not I can probably post some screenshots, but can you post what your 'raw' data looks like and what you want the data to look like (an Excel mock-up would be fine).