topic Pulling all unique values from a data set into a table in QlikView

Pulling all unique values from a data set into a table

Wed, 24 Jun 2015 16:32:00 GMT

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?

Re: Pulling all unique values from a data set into a table

Gysbert_Wassenaar — Wed, 24 Jun 2015 17:05:00 GMT

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

Re: Pulling all unique values from a data set into a table

quriouss — Wed, 24 Jun 2015 17:23:59 GMT

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'.

Re: Pulling all unique values from a data set into a table

Wed, 24 Jun 2015 18:47:33 GMT

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!

Re: Pulling all unique values from a data set into a table

Wed, 24 Jun 2015 19:28:31 GMT

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

Order# Date_Modified

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# ;

Re: Pulling all unique values from a data set into a table

quriouss — Wed, 15 Jul 2015 11:57:05 GMT

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).