Information Propagation in QlikView

[#1]

Hi Gurus,

I have requirement to generate 2 additional QVD files in QlikView with 1 source of excel file.

I am very new to qlikview, Can any one please help me with proper modeling for requirement.

Please find the attached excel file to know about my requirement.

Thanks in Advance,

Muralidhar Reddy N

[MayilVahanan · Accepted solution]

Hi

Table1:

LOAD WHO, TANUM, TRART, Date(Date#(ConfDate,'DD.MM.YYYY'),'DD/MM/YYYY') AS ConfDate, ConfTime, Timestamp(DateTime,'DD/MM/YYYY HH:mm:SS') As DateTime;

LOAD *, Date(Date#(ConfDate,'DD.MM.YYYY'),'DD/MM/YYYY HH:mm:SS')+ Time(ConfTime,'HH:mm:SS') AS DateTime INLINE [

    WHO, TANUM, TRART, ConfDate, ConfTime

    1, 1, 1, 06.05.2014, 17:55:24

    1, 2, 1, 06.05.2014, 18:02:29

    1, 3, 1, 06.05.2014, 20:15:51

    1, 4, 1, 06.05.2014, 20:25:51

    3, 26, 1, 08.05.2014, 10:15:30

    3, 27, 1, 08.05.2014, 10:27:16

    3, 28, 1, 08.05.2014, 16:36:05

    3, 29, 1, 08.05.2014, 17:11:43

    3, 30, 1, 08.05.2014, 17:37:25

    3, 32, 1, 08.05.2014, 20:45:01

    2, 18, 2, 07.05.2014, 18:35:40

    2, 19, 2, 07.05.2014, 19:18:23

    2, 20, 2, 07.05.2014, 23:59:20

    2, 21, 2, 08.05.2014, 0:09:00

];

Table3:

Load WHO, Date(Min(ConfDate)) AS EPAD, Date(Max(ConfDate)) AS LPAD, Time(Min(ConfTime),'HH:mm:SS') AS EPAT, Time(Max(ConfTime),'HH:mm:SS') AS LPAT, Count(TRART) AS NoPAs

Resident Table1 WHERE TRART = 1

Group By WHO;

Join(Table3)

Load WHO, Date(Min(ConfDate)) AS EPID, Date(Max(ConfDate)) AS LPID, Time(Min(ConfTime),'HH:mm:SS') AS EPIT, Time(Max(ConfTime),'HH:mm:SS') AS LPIT, Count(TRART) AS NoPIs

Resident Table1 WHERE TRART = 2

Group By WHO;

Store Table3 into Table3.qvd(Qvd);

DROP Table Table3;

NoConcatenate

Table2:

Load WHO,  Num#(Text(AVGPA)) / Temp AS AVGPA;

Load WHO, Interval(Max(DateTime) - Min(DateTime),'SS') AS AVGPA, Count(TRART) As Temp

Resident Table1 WHERE TRART = 1

Group by WHO;

Join (Table2)

Load WHO,  Num#(Text(AVGPI)) / Temp AS AVGPI;

Load WHO, Interval(Max(DateTime) - Min(DateTime),'SS') AS AVGPI, Count(TRART) As Temp

Resident Table1 WHERE TRART = 2

Group by WHO;

Store Table2 into Table2.qvd(Qvd);

DROP Table Table2;

Hope it helps

[#3]

It is working for my requirement.

Thank you Ramasamy.

Muralidhar Reddy N

[#4]

Hi Ramasamy,

That INLINE is mandatory?

I want to use my excel file which will be getting updated data with changes and new records every 30 Mins.

Thanks

Muralidhar Reddy N.

[MayilVahanan]

hi

For test purpose, i used inline table. You can replace with excel file