Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
alexandermllr
New Contributor II

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
RonaldDoes
Contributor III

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
New Contributor II

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?

RonaldDoes
Contributor III

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
New Contributor II

Re: Qlikview if function - greate new line

Hi Ronald,

perfect! Thank you very much

alexandermllr
New Contributor II

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

RonaldDoes
Contributor III

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
New Contributor II

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

Community Browser