Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
QV85
Contributor
Contributor

Load script interval

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 ??

 

QV85_0-1600948047698.png

 

1 Solution

Accepted Solutions
MarcoWedel

Hi,

another example might be:

 

QlikCommunity_Thread_t5_New-to-QlikView_Load-script-interval_td-p_1746784_Pic1.PNG

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

View solution in original post

5 Replies
sunny_talwar

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;
QV85
Contributor
Contributor
Author

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

 

MarcoWedel

Hi,

another example might be:

 

QlikCommunity_Thread_t5_New-to-QlikView_Load-script-interval_td-p_1746784_Pic1.PNG

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

View solution in original post

QV85
Contributor
Contributor
Author

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

MarcoWedel

you're welcome

please close your thread

thanks

Marco