Solved: Re: add row conditionally on join - Qlik Community

cuniberto · ‎2017-12-14

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.

sunny_talwar · ‎2017-12-14

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

Anil_Babu_Samineni · ‎2017-12-14

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

sunny_talwar · ‎2017-12-14

Why does COD3 not need the begin date?

cuniberto · ‎2017-12-14

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 · ‎2017-12-14

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

sunny_talwar · ‎2017-12-14

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;

cuniberto · ‎2017-12-14

thanks a lot!