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

load two tables: first with data and second with + or - sign

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)

CAMOMMDescriptionGian.
10buy with order+
20sell with order-
22returned from customer+
70

internal load

+
71internal 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,

CDARMMDTOPMMCAMOMMQTMOMM
3722/8002016012710100
3722/80020160215205
1257201601122034
1257201603021070
3722/80020160222708
3722/80020160222718

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

1 Solution

Accepted Solutions
sunny_talwar

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;

Capture.PNG

View solution in original post

7 Replies
sunny_talwar

What is your desired output here?

dsharmaqv
Creator III
Creator III

Load both of your table. Since CAMOMM is common field it will auto matically linked and then you can filter and display data.

Anonymous
Not applicable
Author

Hi,

Hopefully I have understood your requirement.

Is this what your looking for?

Preview_2Tables_exclude.PNG

See second table below to exclude 70 & 71

2Tables_exclude.PNG

Test app attached.

Hope this helps!

Not applicable
Author

what I need to obtain is this, and absolutely in the load script:

CDARMMYEARCAMOMMGian.IN goingOUT going
3722/800201610+1000
3722/800201620-0-5
1257201620-0-34
1257201610+700

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

Not applicable
Author

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 applicable
Author

not exactly, see my answer to Sunny T

sunny_talwar

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;

Capture.PNG