topic Re: Create a table with data from another table in QlikView

Create a table with data from another table

Anonymous — Mon, 03 Aug 2015 19:39:26 GMT

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!

Re: Create a table with data from another table

maxgro — Mon, 03 Aug 2015 19:50:59 GMT

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;

Re: Create a table with data from another table

Anonymous — Tue, 04 Aug 2015 12:56:54 GMT

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

Re: Create a table with data from another table

puttemans — Tue, 04 Aug 2015 13:07:58 GMT

Hi,

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

Re: Create a table with data from another table

Anonymous — Tue, 04 Aug 2015 13:18:26 GMT

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

Re: Create a table with data from another table

puttemans — Tue, 04 Aug 2015 13:45:17 GMT

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;

Re: Create a table with data from another table

maxgro — Tue, 04 Aug 2015 13:58:37 GMT

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;