Create a table with data from another table

[Anonymous]

Hello dear community, I have the following question that I hope you can help me with:

I have the following data about products and seller's ID, on which I'm only interested in the first 10.

N°	Product	Price	Quantity	Seller ID
1	Computer	10	1500	1
2	Banana	10	1400	2
3	Apple	10	1300	3
4	Phone	10	1200	4
5	Cell Phone	10	1100	5
6	Mouse	10	1000	6
7	Keyboard	10	900	7
8	HeadPhone	10	800	8
9	Screen	10	700	9
10	Printer	10	600	10
11	Scanner	10	500	11
12	Pen	10	400	12
13	Tablet	10	300	13
14	Smartphone	10	200	14
15	FlashDrive	10	100	15

When I apply a change in the sales policies, the sales also have been modified::

N°	Product	Price	Quantity	Seller ID
1	Computer	10	1500	1
2	FlashDrive	10	1400	15
3	Smartphone	10	1300	14
4	Tablet	10	1200	13
5	Cell Phone	10	1100	5
6	Mouse	10	1000	6
7	Keyboard	10	900	7
8	HeadPhone	10	800	8
9	Screen	10	700	9
10	Printer	10	600	10
11	Scanner	10	500	11
12	Pen	10	400	12
13	Phone	10	300	4
14	Apple	10	200	3
15	Banana	10	100	2

As you can see, with the sales policies changes, the Seller's ID 15,14 and 13 were highly benefited.

What i want to do, is that in another table, just put the data of the sellers that in the first table were under the first 10, and in the second table (with the policies changes) are above the first 10 (15, 14 and 13):

N°	Product	Price	Quantity	Seller ID
2	FlashDrive	10	1400	15
3	Smartphone	10	1300	14
4	Tablet	10	1200	13

Is there anyway that this can be done?

Thanks a lot!

[maxgro]

T1:

LOAD N°,

     Product,

     Price,

     Quantity,

     [Seller ID]

FROM

[https://community.qlik.com/thread/175069]

(html, codepage is 1252, embedded labels, table is @1);

T2:

NoConcatenate

LOAD N°,

     Product,

     Price,

     Quantity,

     [Seller ID]

FROM

[https://community.qlik.com/thread/175069]

(html, codepage is 1252, embedded labels, table is @2);

Tmp:

load [Seller ID] as SellerGT10 Resident T1 where N° > 10;

Result:

NoConcatenate

load * Resident T2 where N° <= 10 and exists (SellerGT10, [Seller ID]);

DROP Table Tmp;

DROP Table T1, T2;

[Anonymous]

Hello Massimo, thanks for the answer, but I still have a question though:
I have another problem, in another set of data  that  I have, I don't have the "N°" column, but I need to arrange the top 10 sellers using the "quantity" column. How can I do that?

Thx

[puttemans]

Hi,

You load the table descending based upon quantity, and you add an autonumber(). As of there, you do have the No column

[Anonymous]

Just like that? I put the autonumber() and it takes automaticly the quantity as the right parameter?

[puttemans]

Attention, you need to define the parameter by which you want to load/order. For example:

LOAD

     Product,

     Price,

     Quantity,

     [Seller ID],

    Autonumber () as Nr

FROM table x

Order by Quantity desc;

[maxgro]

if I understand you can resident load the data order by quantity or quantity desc and add a counter; then you can use the counter to filter the first 10, etc, ..... (bold). I just did for one of the table, T1, you can expand to T2.

T1:

LOAD N°,

     Product,

     Price,

     Quantity,

     [Seller ID]

FROM

[https://community.qlik.com/thread/175069]

(html, codepage is 1252, embedded labels, table is @1);

T2:

NoConcatenate

LOAD N°,

     Product,

     Price,

     Quantity,

     [Seller ID]

FROM

[https://community.qlik.com/thread/175069]

(html, codepage is 1252, embedded labels, table is @2);

Tmp2:

load rowno() as rowno, [Seller ID] as SellerGT10, Quantity Resident T1

order by Quantity desc;

Tmp:

NoConcatenate load * resident Tmp2 where rowno > 10;

drop table Tmp2;

Result:

NoConcatenate

load * Resident T2 where N° <= 10 and exists (SellerGT10, [Seller ID]);

DROP Table Tmp;

DROP Table T1, T2;