topic Re: Need help in generating Rows in Load Script in QlikView

Need help in generating Rows in Load Script

Fri, 09 Oct 2015 07:33:03 GMT

Hi Team,

As per the requirement i need to generate a row in the table. For a any given server on a given date there will always be on record in with UP status and if the server is down on that then only one record with DOWN status with be inserted in table its duration.

Date	Server	Status	Duration
21/09/2015	A	UP	12
21/09/2015	B	UP	112
21/09/2015	B	DOWN	78
22/09/2015	A	UP	32
22/09/2015	A	DOWN	32
22/09	B	UP	455

In the above table Server A has only UP status on 21/09/2015 , So we need to generate a new row with DOWN Status with duration as 0.

The expected output will be

Date	Server	Status	Duration
21/09/2015	A	UP	12
21/09/2015	A	DOWN	0
21/09/2015	B	UP	112
21/09/2015	B	DOWN	78
22/09/2015	A	UP	32
22/09/2015	A	DOWN	32
22/09	B	UP	455
22/09	B	DOWN	0

Please help hic‌jontydkpi‌@

Re: Need help in generating Rows in Load Script

swuehl — Fri, 09 Oct 2015 07:42:17 GMT

Maybe like this:

INPUT:
LOAD * INLINE [
Date, Server, Status, Duration
21/09/2015, A, UP, 12
21/09/2015, B, UP, 112
21/09/2015, B, DOWN, 78
22/09/2015, A, UP, 32
22/09/2015, A, DOWN, 32
22/09/2015, B, UP, 455
];


COMB:
LOAD DISTINCT Date Resident INPUT;


JOIN
LOAD DISTINCT Server RESIDENT INPUT;


JOIN
LOAD Distinct Status Resident INPUT;


LEFT JOIN (COMB)
LOAD * Resident INPUT;


DROP TABLE INPUT;


RESULT:
NoConcatenate LOAD Date, Server, Status, Alt(Duration,0) as Duration
Resident COMB
ORDER BY Date, Server, Status desc;


DROP Table COMB;

Re: Need help in generating Rows in Load Script

avinashelite — Fri, 09 Oct 2015 07:46:19 GMT

please find the attachment for the solution

Re: Need help in generating Rows in Load Script

jagan — Fri, 09 Oct 2015 07:54:38 GMT

Hi,

Try like this

Data:

LOAD Date,

Server,

Status,

Duration

FROM

[sample data 2.xlsx]

(ooxml, embedded labels, table is Sheet1);

Temp:

NoConcatenate

LOAD

Date,

Server,

0 AS Duration

RESIDENT Data;

LEFT JOIN(Temp)

LOAD

INLINE [

Status

DOWN];

Concatenate(Data)

LOAD

RESIDENT Temp;

DROP TABLE Temp;

Final:

NoConcatenate

LOAD

Date,

Server,

Status,

Sum(Duration) AS Duration

RESIDENT Data

GROUP BY Date,

Server,

Status;

DROP TABLE Data;

Regards,

Jagan.

Re: Need help in generating Rows in Load Script

avinashelite — Fri, 09 Oct 2015 07:56:27 GMT

LOAD Date,

Server,

Status,

Duration

FROM

[https://community.qlik.com/thread/186121]

(html, codepage is 1252, embedded labels, table is @1);

T1:

LOAD Date as D,

Server as S,

count(Status) as Count_status

Resident

group by

Date, Server;

Concatenate(T)

LOAD D as Date,

S as Server,

'DOWN' as Status,

0 as Duration

Resident

where Count_status=1;

Drop Table T1;

Re: Need help in generating Rows in Load Script

Fri, 09 Oct 2015 08:33:47 GMT

Thanks its working

Re: Need help in generating Rows in Load Script

avinashelite — Fri, 09 Oct 2015 09:41:43 GMT

can you please mark the correct answer and close this thread , so that it will be helpful other

Re: Need help in generating Rows in Load Script

jagan — Fri, 09 Oct 2015 09:55:35 GMT

Hi Binod,

What if Status UP record missed? Did you tested this scenario?

Regards,

Jagan.

Re: Need help in generating Rows in Load Script

avinashelite — Fri, 09 Oct 2015 10:11:15 GMT

Hi Jagan,

My solution is based on this statement below

" there will always be on record in with UP status and if the server is down on that then only one record with DOWN status"

Re: Need help in generating Rows in Load Script

Fri, 09 Oct 2015 10:16:19 GMT

Hi Jagan and avinash,

Yes there will be always one record with UP for a server on a given date . Only if the server is down on that day then a two records will be inserted in the table one with UP and DOWN.

Re: Need help in generating Rows in Load Script

avinashelite — Fri, 09 Oct 2015 10:35:56 GMT

Thanks for confirming ...can you please mark the correct answer and close this thread