topic Re: Split field and add dates in QlikView

Split field and add dates

djnqlear — Fri, 15 Nov 2024 23:45:45 GMT

Hi i have a field with all 365 days were days are marked either 01,03 or 04.

There are 3 fields stream(5), year(2020) and note(03030101040101010101...........)

I want to split and date note like

2020-01-01 03

2020-01-02 03

2020-01-03 01

is it possible? se sampledata.qvw

Re: Split field and add dates

Kushal_Chawda — Wed, 21 Oct 2020 21:56:08 GMT

@djnqlear what is logic to get date from long Note field value?

Re: Split field and add dates

Vegar — Wed, 21 Oct 2020 21:58:30 GMT

I'm thinking that you could do something similar to this.

For i =0 to 264

Load

stream,

year,

dayname(yearstart(makedate(year)) + $(i)) as Date,

mid(note, $(i) *2+1) as note

From source;

Next i

Re: Split field and add dates

djnqlear — Wed, 21 Oct 2020 21:59:30 GMT

I want the dates marked with 03 or 04

Daniel

Re: Split field and add dates

djnqlear — Wed, 21 Oct 2020 22:19:00 GMT

first 2 char in note field is 2020-01-01 next 2 2020-01-02 ....

Re: Split field and add dates

rwunderlich — Wed, 21 Oct 2020 22:23:01 GMT

For example, if your data was in a Resident table "Data" (adjust to where ever your input is).

LOAD
Stream,
Year,
Date(MakeDate(Year) + IterNo()) as Date,
Mid(Note, IterNo() * 2 - 1, 2) as NoteValue
Resident Data
While IterNo() * 2 < len(Note)
;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Split field and add dates

djnqlear — Wed, 21 Oct 2020 22:47:16 GMT

Dates missing 2019-01-01?

Re: Split field and add dates

Kushal_Chawda — Wed, 21 Oct 2020 23:37:06 GMT

@djnqlear not sure if I completely understand . But gave it a shot

Data: LOAD Year, Stream date(makedate(Year)+IterNo()-1) as Date, mid(Note, IterNo()*2-1,2) as Note, Note as Note_Original FROM Table while IterNo() <= (len(Note)/2);