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

Ho to creat a new table from two different table?

Hello, everybody.

I'm new in Qlik Sense and I need some help.

I have two tables:

Item

Cathegory

Category

Item.no

Category

Description

1Milk"Milk A"
2Bologna"Pork"
3Pasta"Fusilli"
4Milk"Milk B"
5Bologna"Beef"

and

Ledger

Item.noSalesDate
11002011.01.01
52002011.02.02
23002012.03.03
44002012.03.03
15002013.01.01

I need create a new variable in script to Qlik sense script editor from two different tables :

: Category, sum(sales) by Category

It have to look:

Header 1Header 2
Milk

1000 // 100+400+500

Bologna500 //  200+300 

If I'm using

[KA1]:

LOAD

Category as K

FROM [lib://ITEM/item.qvd]

(qvd);

join LOAD

    Sales as AP

FROM [lib://ITEM LEDGER/item_ledger.qvd]

(qvd);

I've got a cross table and duplicated values:

KAP
Milk100
Milk200
Milk300
Milk400
Milk500
Bologna100
Bologna200
.......

I tried mapping too:

[KATE]:

LOAD

    item.no as IK,

    Category as IC

FROM [lib://ITEM/item.qvd]

(qvd);

[keysales]:

LOAD

    item.no as Ikey,

    sales as Isales

FROM [lib://ITEM LEDGER/item_ledger.qvd]

(qvd);

[map2]:

mapping    

LOAD

    Ikey,

    Isales

resident [keysales];

[K2]:

load *, ApplyMap ('map2', IK )

    as sumA

   Resident  [KATE]

It's not working too. I've missing a data. For item.no 1, sumA is only 100.

How to create a new variable with cathegory, and sum sales?

Thanks for help in advance.

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

I think you can just load these tables with a simple join:

[KA1]:

LOAD *

FROM [lib://ITEM/item.qvd]

(qvd);

JOIN

LOAD *

FROM [lib://ITEM LEDGER/item_ledger.qvd]

(qvd);

You should see what you need in a table using Category as a dimension and Sum(Sales) as an expression.

It may even by unnecessary to join them. Allow QV to take care of the associations.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

5 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

I think you can just load these tables with a simple join:

[KA1]:

LOAD *

FROM [lib://ITEM/item.qvd]

(qvd);

JOIN

LOAD *

FROM [lib://ITEM LEDGER/item_ledger.qvd]

(qvd);

You should see what you need in a table using Category as a dimension and Sum(Sales) as an expression.

It may even by unnecessary to join them. Allow QV to take care of the associations.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sasiparupudi1
Master III
Master III

Untitled.jpghi

please try the following way

x:

LOAD * Inline

[

Item.no,Category,Description

1,Milk,"Milk A"

2,Bologna,"Pork"

3,Pasta,"Fusilli"

4,Milk,"Milk B"

5,Bologna,"Beef"

];

left join(x)

LOAD * Inline

[

Item.no,Sales,Date

1,100,2011.01.01

5,200,2011.02.02

2,300,2012.03.03

4,400,2012.03.03

1,500,2013.01.01

];

jagan
Luminary Alumni
Luminary Alumni

Hi,

Just use below script

[KA1]:

LOAD

*

FROM [lib://ITEM/item.qvd]

(qvd);

join LOAD

    *

FROM [lib://ITEM LEDGER/item_ledger.qvd]

(qvd);

Now in chart

Dimension: Category

Expression: Sum(Sales)

Regards,

Jagan.

ankitbisht01
Creator
Creator

It is very simple you can use this  and add Description as as dimension  and  sum(Sales) as measure in pivot table:


Category1:

Load * inline

[

Item_no   ,Category   ,Description

1          ,    Milk   , "Milk A"

2   , Bologna, "Pork"

3          , Pasta  ,    "Fusilli"

4          , Milk   ,    "Milk B"

5          , Bologna, "Beef"

];

left join(Category1)

Ledger:

Load * Inline

[

Item_no, Sales,Date

1,     100, 2011.01.01

5,    200, 2011.02.02

2,    300, 2012.03.03

4,    400, 2012.03.03

1,   500, 2013.01.01

];

Final_Table:

Load

Category,

Sales

Resident Category1

where Category <> 'Pasta';

Drop Table Category1

Not applicable
Author

Thank you for help.