Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, everybody.
I'm new in Qlik Sense and I need some help.
I have two tables:
Item
Cathegory
Category
Item.no | Category | Description |
---|---|---|
1 | Milk | "Milk A" |
2 | Bologna | "Pork" |
3 | Pasta | "Fusilli" |
4 | Milk | "Milk B" |
5 | Bologna | "Beef" |
and
Ledger
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 |
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 1 | Header 2 |
---|---|
Milk | 1000 // 100+400+500 |
Bologna | 500 // 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:
K | AP |
---|---|
Milk | 100 |
Milk | 200 |
Milk | 300 |
Milk | 400 |
Milk | 500 |
Bologna | 100 |
Bologna | 200 |
.... | ... |
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.
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.
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.
hi
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
];
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.
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
Thank you for help.