topic add row conditionally on join in QlikView

add row conditionally on join

cuniberto — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: add row conditionally on join

Anil_Babu_Samineni — Thu, 14 Dec 2017 15:51:10 GMT

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

Re: add row conditionally on join

sunny_talwar — Thu, 14 Dec 2017 15:54:11 GMT

Why does COD3 not need the begin date?

Re: add row conditionally on join

cuniberto — Thu, 14 Dec 2017 16:04:38 GMT

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

Re: add row conditionally on join

cuniberto — Thu, 14 Dec 2017 16:05:29 GMT

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

sunny_talwar — Thu, 14 Dec 2017 16:08:32 GMT

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;

Re: add row conditionally on join

cuniberto — Thu, 14 Dec 2017 17:46:23 GMT

thanks a lot!