Filling missing fields with data depending on Date

[eugeniyaromanov]

Hi all!

I've got a problem and don't know how to solve it.

May be someone has an idea...

I've got a table:

Number	Process	DateStart	DateEnd	TransactionDate	TransactionNumber
111	Process1	01.06.2017	09.06.2017	02.06.2017	F1
111				03.06.2017	F2
111				10.06.2017	F3
111	Process2	11.06.2017	15.06.2017	12.06.2017	F4
222	Process3	09.06.2017	15.06.2017	10.06.2017	F5
222				12.06.2017	F6

I need to fill fields: process, DateStart, DateEnd depending on TransactionDate

If TransactionDate fits between DateStart and DateEnd, fields should be filled, if not, not filled.

Final table should be like:

Number	Process	DateStart	DateEnd	TransactionDate	TransactionNumber
111	Process1	01.06.2017	09.06.2017	02.06.2017	F1
111	Process1	01.06.2017	09.06.2017	03.06.2017	F2
111	-	-	-	10.06.2017	F3
111	Process2	11.06.2017	15.06.2017	12.06.2017	F4
222	Process3	09.06.2017	15.06.2017	10.06.2017	F5
222	Process3	09.06.2017	15.06.2017	12.06.2017	F6

Thanks in advance!

[antoniotiman]

Hi Eugeniya,

LOAD Number,
If(Len(Trim(DateStart)) > 0 and Len(Trim(DateEnd)) > 0,
If(TransactionDate >= DateStart and TransactionDate <= DateEnd,Process),
If(TransactionDate >= Previous(DateStart) and TransactionDate <= Previous(DateEnd),Peek(Process))) as Process,
If(Len(Trim(DateStart)) > 0 and Len(Trim(DateEnd)) > 0,
If(TransactionDate >= DateStart and TransactionDate <= DateEnd,DateStart),
If(TransactionDate >= Previous(DateStart) and TransactionDate <= Previous(DateEnd),Peek(DateStart))) as DateStart,
If(Len(Trim(DateStart)) > 0 and Len(Trim(DateEnd)) > 0,
If(TransactionDate >= DateStart and TransactionDate <= DateEnd,DateEnd),
If(TransactionDate >= Previous(DateStart) and TransactionDate <= Previous(DateEnd),Peek(DateEnd))) as DateEnd,
TransactionDate,
TransactionNumber
FROM
"https://community.qlik.com/message/1311319"
(html, codepage is 1252, embedded labels, table is @1);

Regards,

Antonio

[qliksus]

Use the data transforamtion in the file wizard to fill the blank values with the previous row and then using the flag created below you can update to all fields

Data:
load *
Where flag=1;
LOAD Number,
Process,
DateStart,
DateEnd,
TransactionDate,
if( TransactionDate>=DateStart and TransactionDate<=DateEnd , 1,2) as flag,
TransactionNumber
FROM
Filldata.xlsx
(ooxml, embedded labels, table is Sheet1, filters(
Replace(2, top, StrCnd(null)),
Replace(3, top, StrCnd(null)),
Replace(4, top, StrCnd(null))
));

load *
Where flag=2;
LOAD Number,
'' as Process,
'' as DateStart,
'' as DateEnd,
TransactionDate,
if( TransactionDate>=DateStart and TransactionDate<=DateEnd , 1,2) as flag,
TransactionNumber
FROM
Filldata.xlsx
(ooxml, embedded labels, table is Sheet1, filters(
Replace(2, top, StrCnd(null)),
Replace(3, top, StrCnd(null)),
Replace(4, top, StrCnd(null))
));