Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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))
));