Qlikview if function - greate new line

[alexandermllr]

Hello,

I have this function for all fields in the table:

if(Start_time-Previous(End_time) > '0',previous(End_time),End_time) as End_time,

The problem is that it always overwrites me the field, but I need a new line for that.

How can I solve this problem?

Thanks

[Peter_Cammaert]

You mean that in your script you want to create new rows when specific conditions are met?

Can you post some sample input data and the expected output? Preferably in Excel format.

[alexandermllr]

Yes that's what I want.

I have added a Excel Sheet.

[shanky1907]

Please give more detail about the below query:

What is the logic of these entries & if according to the dimension, one's end time is next start time then why we have void in between?

[RonaldDoes · Accepted solution]

Hi Alexander,

I think this will suit your requirements.

Data:
LOAD Key,
     Name,
     Start_Time,
     End_Time
FROM
Data.xlsx
(ooxml, embedded labels, table is Tabelle1);

Temp_Data:
NoConcatenate
LOAD Key,
     Name,
     If(Start_Time > Previous(End_Time), Previous(End_Time)) AS Start_Time,
     If(Start_Time > Previous(End_Time), Start_Time) AS End_Time
Resident Data ORDER BY Key, Name, Start_Time ASC;

Concatenate (Data)
LOAD Key,
     Name,
     Start_Time,
     End_Time
Resident Temp_Data WHERE NOT IsNull(Start_Time);

DROP Table Temp_Data;

Please find the example attached.

Hope this helps you.

With kind regards,

Ronald

[alexandermllr]

Hi Ronald,

perfect! Thank you very much

[alexandermllr]

Hi,

I have one problem now.

Sometimes this script is generating some lines at the start/end of every key.

I don't know why, it also takes for me random Start_time/End_time values.

How can I do this script only between the min and max Start_time?

Thanks

[RonaldDoes]

Hi Alexander,

That was of course to be expected. My bad. This should do it.

If(Key = Previous(Key), If(Start_Time > Previous(End_Time), Previous(End_Time))) AS Start_Time,  
If(Key = Previous(Key), If(Start_Time > Previous(End_Time), Start_Time)) AS End_Time

[alexandermllr]

Hi Ronald,

thank you so much!

I have added an "and" instead of the comma.

Then it worked