Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everybody,
I have this scenario:
two tables
1) the first is a list of description each one with the relative sign (it's about the description of movements of goods)
CAMOMM | Description | Gian. |
---|---|---|
10 | buy with order | + |
20 | sell with order | - |
22 | returned from customer | + |
70 | internal load | + |
71 | internal unload | - |
as you see when I buy a product the quantities in the warehouse increase ( + ) as well as when a customer return a product, while when I sell a product the quantities decrease;
2) in the second table I have all the movements that interested my warehouse,
CDARMM | DTOPMM | CAMOMM | QTMOMM |
---|---|---|---|
3722/800 | 20160127 | 10 | 100 |
3722/800 | 20160215 | 20 | 5 |
1257 | 20160112 | 20 | 34 |
1257 | 20160302 | 10 | 70 |
3722/800 | 20160222 | 70 | 8 |
3722/800 | 20160222 | 71 | 8 |
where CDARMM is the article of the product bought or sold, DTOPMM is the date of selling of buying, CAMOMM is the description in table one and QTMOMM is the quantity bought or sold.
NOW, the thing I need to do is to load the above data with a script so that I obtain a table where, for each article, I have the item (CDARMM), year, the total ingoing quantity ( + ) and the total outgoing quantity ( - ) ; furthermore while summing the quantities I have to exclude the movements that came with certain CAMOMM (in my case I have to ignore CAMOMM = 70 or 71)
I'll have several years of data to load, each year is in a different file, while le first above mentioned table is always the same (see the first point).
I tried several solution, but I'm not able to find the solution
someone can help me?
thanks
giuliano
May be like this?
Warehouse:
LOAD CDARMM,
Date(Date#(DTOPMM, 'YYYYMMDD'), 'YYYYMMDD') as DTOPMM,
CAMOMM,
QTMOMM
FROM
[https://community.qlik.com/thread/245330]
(html, codepage is 1252, embedded labels, table is @2)
Where not Match(CAMOMM, 70, 71);
Left Join(Warehouse)
LOAD CAMOMM,
Description,
Gian.
FROM
[https://community.qlik.com/thread/245330]
(html, codepage is 1252, embedded labels, table is @1);
FinalTable:
LOAD CDARMM,
Year(DTOPMM) as YEAR,
CAMOMM,
Gian.,
If(Gian. = '+', QTMOMM, 0) as [IN going],
If(Gian. = '-', -QTMOMM, 0) as [OUT going]
Resident Warehouse;
DROP Table Warehouse;
What is your desired output here?
Load both of your table. Since CAMOMM is common field it will auto matically linked and then you can filter and display data.
Hi,
Hopefully I have understood your requirement.
Is this what your looking for?
See second table below to exclude 70 & 71
Test app attached.
Hope this helps!
what I need to obtain is this, and absolutely in the load script:
CDARMM | YEAR | CAMOMM | Gian. | IN going | OUT going |
---|---|---|---|---|---|
3722/800 | 2016 | 10 | + | 100 | 0 |
3722/800 | 2016 | 20 | - | 0 | -5 |
1257 | 2016 | 20 | - | 0 | -34 |
1257 | 2016 | 10 | + | 70 | 0 |
You probably will note that it's a little different from my preceding request (apologize), but I have already many other tables with data that come from a old program for years from 2008 to 2015 and such table are already part of a set analysis. If I obtain 2016 data with the same format of 2015 I save all the time invested in the set analysis structure.
Hope now it's a little bit more clear.
thanks
giuliano
yes I know, but I need in any case to put the sign of the amount (i.e. +100, +70, -5, -34) in the script 'cause I have old data that have such a format and I need to preserve the time I spent for a set analysis I already use from two years ... see also my answer to Sunny T.
not exactly, see my answer to Sunny T
May be like this?
Warehouse:
LOAD CDARMM,
Date(Date#(DTOPMM, 'YYYYMMDD'), 'YYYYMMDD') as DTOPMM,
CAMOMM,
QTMOMM
FROM
[https://community.qlik.com/thread/245330]
(html, codepage is 1252, embedded labels, table is @2)
Where not Match(CAMOMM, 70, 71);
Left Join(Warehouse)
LOAD CAMOMM,
Description,
Gian.
FROM
[https://community.qlik.com/thread/245330]
(html, codepage is 1252, embedded labels, table is @1);
FinalTable:
LOAD CDARMM,
Year(DTOPMM) as YEAR,
CAMOMM,
Gian.,
If(Gian. = '+', QTMOMM, 0) as [IN going],
If(Gian. = '-', -QTMOMM, 0) as [OUT going]
Resident Warehouse;
DROP Table Warehouse;