topic Qlikview if function - greate new line in QlikView

Qlikview if function - greate new line

alexandermllr — Fri, 09 Jun 2017 08:20:55 GMT

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

Re: Qlikview if function - greate new line

Peter_Cammaert — Fri, 09 Jun 2017 08:33:21 GMT

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.

Re: Qlikview if function - greate new line

alexandermllr — Fri, 09 Jun 2017 08:42:53 GMT

Yes that's what I want.

I have added a Excel Sheet.

Re: Qlikview if function - greate new line

shanky1907 — Fri, 09 Jun 2017 12:22:32 GMT

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?

Re: Qlikview if function - greate new line

RonaldDoes — Fri, 09 Jun 2017 12:40:00 GMT

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

Re: Qlikview if function - greate new line

alexandermllr — Fri, 09 Jun 2017 12:54:35 GMT

Hi Ronald,

perfect! Thank you very much

Re: Qlikview if function - greate new line

alexandermllr — Fri, 09 Jun 2017 14:56:06 GMT

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

Re: Qlikview if function - greate new line

RonaldDoes — Fri, 09 Jun 2017 18:14:53 GMT

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

Re: Qlikview if function - greate new line

alexandermllr — Mon, 12 Jun 2017 07:41:27 GMT

Hi Ronald,

thank you so much!

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

Then it worked