topic Re: Load script interval in QlikView

Load script interval

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

Hi ALL

I get such data [Table 1] and I need to convert them into the form shown in Table 2
I need to do an interval every minute and assign VALUE and Part to this interval
Anyone have any idea how to do this ??

Re: Load script interval

sunny_talwar — Thu, 24 Sep 2020 12:32:33 GMT

Here is one way to do this

Table: LOAD * INLINE [ Part, CreationDate, Value A, 09/24/2020 10:00, 1 A, 09/24/2020 10:03, 0 A, 09/24/2020 10:06, 1 B, 09/24/2020 10:04, 1 B, 09/24/2020 10:06, 0 B, 09/24/2020 10:09, 1 ]; tmpTable: LOAD TimeStamp(MinDate + MakeTime(0, IterNo()-1)) as CreationDate, Round((MinDate + MakeTime(0, IterNo()-1))*24*60) as CreationDateNum While TimeStamp(MinDate + MakeTime(0, IterNo()-1)) <= MaxDate; LOAD Min(CreationDate) as MinDate, Max(CreationDate) as MaxDate Resident Table; Left Join (tmpTable) LOAD Distinct Part Resident Table; Left Join (tmpTable) LOAD Part, Round(CreationDate*24*60) as CreationDateNum, Value Resident Table; DROP Table Table; FinalTable: NoConcatenate LOAD Part, CreationDate, If(Part = Previous(Part), If(Len(Trim(Value)) = 0, Peek('Value'), Value), Value) as Value Resident tmpTable Order By Part, CreationDateNum; DROP Table tmpTable;

Re: Load script interval

QV85 — Thu, 24 Sep 2020 12:51:56 GMT

Thank you very much
unfortunately there is no data in FinalTable
3 Fields, 0 Rows

Re: Load script interval

MarcoWedel — Thu, 24 Sep 2020 21:06:45 GMT

Hi,

another example might be:

table1: LOAD Part, Timestamp#(CreationDate,'MM/DD/YYYY hh:mm') as CreationDate, Value Inline [ Part, CreationDate, Value A, 09/24/2020 10:00, 1 A, 09/24/2020 10:03, 0 A, 09/24/2020 10:06, 1 A, 09/24/2020 10:10, 0 B, 09/24/2020 10:04, 1 B, 09/24/2020 10:06, 0 B, 09/24/2020 10:09, 1 C, 09/24/2020 09:50, 1 C, 09/24/2020 09:55, 0 ]; Join LOAD Fieldvalue('Part',IterNo()) as Part, * While IterNo() <= FieldValueCount('Part'); LOAD Timestamp#(Timestamp(MinCreationDate+(IterNo()-1)*'00:01','MM/DD/YYYY hh:mm'),'MM/DD/YYYY hh:mm') as CreationDate While Timestamp#(Timestamp(MinCreationDate+(IterNo()-1)*'00:01','MM/DD/YYYY hh:mm'),'MM/DD/YYYY hh:mm') <= MaxCreationDate; LOAD Timestamp(Min(Fieldvalue('CreationDate',RecNo())),'MM/DD/YYYY hh:mm') as MinCreationDate, Timestamp(Max(Fieldvalue('CreationDate',RecNo())),'MM/DD/YYYY hh:mm') as MaxCreationDate AutoGenerate FieldValueCount('CreationDate'); NoConcatenate table2: LOAD Part, CreationDate, If(IsNull(Value) and Part=Previous(Part), Peek(Value), Value) as Value Resident table1 Order By Part, CreationDate; DROP Table table1;

hope this helps

regards

Marco

Re: Load script interval

QV85 — Fri, 25 Sep 2020 08:10:24 GMT

Thanks a lot Marco
this is exactly what I was looking for
works perfect

Re: Load script interval

MarcoWedel — Wed, 30 Sep 2020 22:33:28 GMT

you're welcome

please close your thread

thanks

Marco