Make Graph of imported TXT file

[#1]

Hello to you all out there!

I want to make a graph from a txt log file that is being updated every 5 minutes.

First of all how do I import the txt file and make seperate tables of the contents ( I attathed the TXT file).

Then I want to use a Stoplight Graph then will show green when there is nothing in the TXT file, Yellow when there are 2 Aircrafts in the TXT file, RED when their are 5 or more Aircrafts in the TXT File.

I am a QlikView Noob, but i am happy to get to know it better since i noticed it is a Power Application. If i get this to work I'll implement it in our Network,

but for now i am using the Personal Edition!

Any tips and ideas are appreciated!

This is how the contents look like:

fn_carrier ac_registr sched_offb_dep_date sched_offb_dep_time sched_onbl_arr_date sched_onbl_arr_time
---------- ---------- ------------------- ------------------- ------------------- -------------------
EXM KZA 14227 60 14227 630
RP KZO 14225 450 14227 781
RP KZV 14224 540 14227 541
EXM KZA 14227 60 14227 541
AOG KZK 14227 750 14227 936
AOG OFN 14227 314 14227 961
AOG KZM 14221 1410 14227 541
EXM KZE 14227 300 14227 360
AOG EZH 14227 525 14227 661
AOG OFO 14227 325 14227 480
EXM KZA 14227 60 14227 121
AOG OFO 14227 325 14227 1081
EXM KZA 14227 60 14227 633
EXM KZA 14227 60 14227 633
AOG KZK 14227 645 14227 721
AOG KZK 14227 990 14227 1176
AOG OFO 14227 325 14227 600
AOG OFO 14227 325 14227 661
AOG KZK 14227 415 14227 481
AOG KZK 14227 415 14227 601
AOG OFO 14227 325 14227 1021
AOG OFO 14227 325 14227 690
AOG OFM 14226 870 14227 61
EXM KZA 14227 60 14227 360
EXM KZA 14227 60 14227 721
EXM KZE 14227 300 14227 360
AOG EZB 14227 315 14227 345
AOG KZU 14226 720 14227 61
AOG KZK 14227 315 14227 330
AOG KZK 14227 315 14227 601
RP KZV 14224 540 14227 721
AOG OFO 14227 325 14227 361
AOG OFO 14227 325 14227 361
AOG OFO 14227 325 14227 390
AOG KZM 14221 1410 14227 1321
AOG OFO 14227 325 14227 420
AOG OFN 14227 314 14227 421
AOG OFN 14227 314 14227 391

(38 rows affected)

[deepakk]

hi,

In the script level there is button for Table Files. Once clicked on the Table file a pop up will be opened. Sleect the text file.

You will be able to see a wizard. Select FileType as Delimited and select Delimiter as Space. You will get the data in tabluar format . Use a Gauge chart to acheive the Stoplight Graph.

[#3]

Thank you for your quick respons Deepak,

I achieved to do this directly from the SQL database,

I got my tables etc, but I cant figure out to get the Stoplight working correct. I got the Chart but it doesn't function. How do I use expressions.

This is the rule layout;

IF shed_offb_dep_date is =< current date OR shed_offblock_dep_time is =< current date THEN RED

IF shed_onbl_arr_date is == current date OR shed_onbl_arr_date is == current date THEN GREEN

I can understand if this doesn't say enough. Please tell me if so. I appreciate your quick response again!

[deepakk]

HI,

I am attaching a sample application on Stop Light. I didn't get your exact scenario.

I have worked out with the fn_carrier.

If the count of fn_Carrier < 7 then it will display orange. between 7 to 25 it will display green and more than 25 will display red.

Once you have selected the style from the gauge chart properties. Go to Presentation tab( Make sure there is no tick in Auto width Segment).

Set the min and max value. Now you can define each segments lower bound starting from the lowest.

Check out the application and let me know if you have any queries.

[deepakk]

Ops missed the attachment in the above post.

[#6]

I will have a look and notice you as soon as possible. Thanking your efforts!

[#7]

I tried to cobine your file with my new file that connects to a database instead of a textfile. But I keep getting errors. Can you tell me how to upload qlikview files?

The expressions are as follows and are specific.

NAYAK AOG DASHBOARD

COUNT IF:

SCHED_OFB_DEP_DATE ≤ CURRENT DATE

SCHED_OFB_DEP_TIME ≤ CURRENT TIME

SCHED_ONBL_DEP_DATE ≥ CURRENT DATE

SCHED_ONBL_DEP_TIME ≥ CURRENT TIME

GREEN 0 ≤ AOG ≤ 2

YELLOW 3 ≤ AOG ≤ 4

RED AOG ≥ 5

The script till now (connected to ODBC database instead of textfile) is as follows:

SET ThousandSep='.';

SETDecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;€ -#.##0,00';

SET TimeFormat='h:mm:ss';

SET DateFormat='D-M-YYYY';

SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';

SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec' ;

SET DayNames='ma;di;wo;do;vr;za;zo';

// verbinding maken met database://

ODBC CONNECT TO sybase (XUserId is ceYTdZFMMD, XPassword is JPeHcQVMSbbOTcBPRRMON);

LOAD

date

("sched_offb_dep_date" + 26298) as StartDate

,

time

("sched_offb_dep_time"/1440,'hh:mm') as StartTime

,

date

("sched_onbl_arr_date" +26298) as EndDate

,

time

("sched_onbl_arr_time"/1440,'hh:mm') as EndTime

,

//SQL SELECT sched_offb_dep_date, sched_offb_dep_time, sched_onbl_arr_date, sched_onbl_arr_time

//FROM prod.dbo."ac_util_netl_two_hist";

"ac_registr"

as

Aircraft;

// de kolommen en later voorwaarden selecteren voor type onderhoud voor selecties in het dashboard//

SQL SELECT

ac_registr,

sched_offb_dep_date,

sched_offb_dep_time,

sched_onbl_arr_date,

sched_onbl_arr_time

FROM

prod.dbo."ac_util_netl_two_hist"

where

(

/*START OF E190 */

"ac_registr"='EZA'

or "ac_registr"='EZB' or "ac_registr"='EZC' or "ac_registr"='EZD' or "ac_registr"='EZE' or

"ac_registr"='EZF'

or "ac_registr"='EZG' or "ac_registr"='EZH' or "ac_registr"='EZI' or "ac_registr"='EZK' or "ac_registr"='EZL' or

"ac_registr"='EZM'

or "ac_registr"='EZN' or "ac_registr"='EZO' or "ac_registr"='EZP' or "ac_registr"='EZR' or "ac_registr"='EZS'

or

/*END OF E190 */

/*START OF F70 */

"ac_registr"='JCH'

or "ac_registr"='JCT' or "ac_registr"='KZB' or "ac_registr"='KZC' or "ac_registr"='KZD'

or

"ac_registr"='KZE'

or "ac_registr"='KZE' or "ac_registr"='KZF' or "ac_registr"='KZG' or "ac_registr"='KZH' or "ac_registr"='KZI'

or

"ac_registr"='KZK'

or "ac_registr"='KZL' or "ac_registr"='KZL' or "ac_registr"='KZN' or "ac_registr"='KZP' or "ac_registr"='KZR'

or

"ac_registr"='KZS'

or "ac_registr"='KZT' or "ac_registr"='KZU' or "ac_registr"='KZV' or "ac_registr"='KZW' or "ac_registr"='WXA'

or

"ac_registr"='WXC'

or "ac_registr"='WXD'

or

/*END OF F70 */

/*START OF F100 */

"ac_registr"='OFL'

or "ac_registr"='OFM' or "ac_registr"='OFN' or "ac_registr"='OFO' or "ac_registr"='OFP'

/*END OF F100 */

);

LOAD

"fn_carrier"

as

Maintenance;

SQL SELECT

fn_carrier

FROM

prod.dbo."ac_util_netl_two_hist"

where

("fn_carrier"='AOG' or "fn_carrier"='DAM' or "fn_carrier"='EXM' or

"fn_carrier"='RP');

LOAD

date

(now(1), 'DD-MM-YYYY') as CurrentDate

,

time

(now(1), 'hh:mm') as

CurrentTime

AUTOGENERATE

1

How do I COMBINE all this into one light gauge!

[deepakk]

hi ,

Dont copy the script. Just check the chart which i have created in that file.

use reply link on right top.

there will option tab which will have the option of uploading the file.

[#9]

Hello Deepak,

I am probably getting annoying by now 😛 But as far as I can notice it does NOT use this formule:

COUNT IF:

SCHED_OFB_DEP_DATE ≤ CURRENT DATE

SCHED_OFB_DEP_TIME ≤ CURRENT TIME

SCHED_ONBL_DEP_DATE ≥ CURRENT DATE

SCHED_ONBL_DEP_TIME ≥ CURRENT TIME

GREEN 0 ≤ AOG ≤ 2

YELLOW 3 ≤ AOG ≤ 4

RED AOG ≥ 5

What extra expressions do i use to:

Show one aircraft only ONCE if it is in the list.

Then have a sum of all FN Carriers.

Thank you in advance!

[deepakk · Accepted solution]

hi,

Add this code in the expression.

COUNT( IF(SCHED_OFB_DEP_DATE ≤ CURRENT DATE and

SCHED_OFB_DEP_TIME ≤ CURRENT TIME and

SCHED_ONBL_DEP_DATE ≥ CURRENT DATE and

SCHED_ONBL_DEP_TIME ≥ CURRENT TIME,

Field Name))

Now this will give output in numbers eg 4

Now go to Presentation and add the min and max value.

eg: min 0 and max 100

Since we have three conditions we need to add three segements in the segment setup.

Segment1 : add lower bound =2 and color green

Segment2: add lower bound 4 and color yellow

Segment3: add lower bound 5 and color Red.

I hope this helps