Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
alexandermllr
Contributor

Qlikview if function - greate new line

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

1 Solution

Accepted Solutions
Partner
Partner

Re: Qlikview if function - greate new line

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

8 Replies

Re: Qlikview if function - greate new line

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
Contributor

Re: Qlikview if function - greate new line

Yes that's what I want.

I have added a Excel Sheet.

shanky1907
Contributor II

Re: Qlikview if function - greate new line

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?

Partner
Partner

Re: Qlikview if function - greate new line

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
Contributor

Re: Qlikview if function - greate new line

Hi Ronald,

perfect! Thank you very much

alexandermllr
Contributor

Re: Qlikview if function - greate new line

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

Partner
Partner

Re: Qlikview if function - greate new line

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
Contributor

Re: Qlikview if function - greate new line

Hi Ronald,

thank you so much!

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

Then it worked