Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
cuniberto
New Contributor III

add row conditionally on join

Hi everyone,

i have a table with warehouse movement.

I need to add the begin and end period value if not present.

begin date 01-10-2016

end date 30-09-2017

movement table:

Code               date               movement quantity

COD1               03-10-2016     -3

COD1               03-06-2017     -7

COD1               03-07-2017     +20

COD1               30-09-2017     -3

COD2               03-10-2016     -3

COD2               10-05-2017     -7

COD2               20-06-2017     +20

COD3               30-09-2016     -3

COD3               25-03-2017     -7

COD3               11-04-2017     +20

COD3               30-09-2017     -3

in the movement table i have to add for the COD1 the date of the beginning period and not the date of the ending period (in that date there is a movement), for the COD2 both date, for COD3 none.

1 Solution

Accepted Solutions

Re: add row conditionally on join

I would do something like this

Table:

LOAD Code,

     date,

     [movement quantity],

     AutoNumber(Code&Num(date)) as Key

FROM ....;

TempTable:

LOAD * INLINE [

date

01-10-2016

30-09-2017

];


Left Join (TempTable)

LOAD DISTINCT Code

Resident Table;


Concatenate (Table)

LOAD Code,

     date,

     0 as [movement quantity]

Resident TempTable

Where not Exists(Key, AutoNumber(Code&Num(date)))


DROP Table TempTable;

View solution in original post

6 Replies

Re: add row conditionally on join

Are you going to have this?

movement table:

Code              date              movement quantity

COD1              01-10-2016    0

......

COD1              03-10-2016    -3

COD1              03-06-2017    -7

COD1              03-07-2017    +20

COD1              30-09-2017    -3

COD2              01-10-2016    0

.....

COD2              03-10-2016    -3

COD2              10-05-2017    -7

COD2              20-06-2017    +20

COD3              30-09-2016    -3

COD3              25-03-2017    -7

COD3              11-04-2017    +20

COD3              30-09-2017    -3

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: add row conditionally on join

Why does COD3 not need the begin date?

cuniberto
New Contributor III

Re: add row conditionally on join

yes, i need the final result is like you suggest

Code              date              movement quantity

COD1              01-10-2016    0

......

COD1              03-10-2016    -3

COD1              03-06-2017    -7

COD1              03-07-2017    +20

COD1              30-09-2017    -3


COD2              01-10-2016    0

.....

COD2              03-10-2016    -3

COD2              10-05-2017    -7

COD2              20-06-2017    +20

....

COD2              30-09-2017    0

COD3              01-10-2016    -3

COD3              25-03-2017    -7

COD3              11-04-2017    +20

COD3              30-09-2017    -3

cuniberto
New Contributor III

Re: add row conditionally on join

sorry, there was a mistake...

the final table i need to hawe is:

Code              date              movement quantity

COD1              01-10-2016    0

......

COD1              03-10-2016    -3

COD1              03-06-2017    -7

COD1              03-07-2017    +20

COD1              30-09-2017    -3


COD2              01-10-2016    0

.....

COD2              03-10-2016    -3

COD2              10-05-2017    -7

COD2              20-06-2017    +20

....

COD2              30-09-2017    0

COD3              01-10-2016    -3

COD3              25-03-2017    -7

COD3              11-04-2017    +20

COD3              30-09-2017    -3

Re: add row conditionally on join

I would do something like this

Table:

LOAD Code,

     date,

     [movement quantity],

     AutoNumber(Code&Num(date)) as Key

FROM ....;

TempTable:

LOAD * INLINE [

date

01-10-2016

30-09-2017

];


Left Join (TempTable)

LOAD DISTINCT Code

Resident Table;


Concatenate (Table)

LOAD Code,

     date,

     0 as [movement quantity]

Resident TempTable

Where not Exists(Key, AutoNumber(Code&Num(date)))


DROP Table TempTable;

View solution in original post

cuniberto
New Contributor III

Re: add row conditionally on join

thanks a lot!