Multiple unlinked excel spreadsheets

Wed, 26 Mar 2014 20:55:38 GMT

I have a series of excel spreadsheets with data (about 20), but each spreadsheet is for a particular year. The headings in each spreadsheet are the same. Is it possible to use QlikView to search all of the spreadsheets in one dashboard? I tried to do this, but ran into difficulties when QlikView assumed that data was linked because the column headings are the same and they contain similar data in different years. One work-around was to rename the column headings that I didn't want linked (shown by the key symbol in QlikView). However, this is a timely and onerous work-around. Is there a better solution? Thanks for your thoughts.

Re: Multiple unlinked excel spreadsheets

Wed, 26 Mar 2014 21:09:11 GMT

My script:

///$tab Main

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;-$#,##0.00';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

///$tab 1993event

LOAD PSU,

CASEID,

[1993CASENO],

[1993STRATIF],

VERSION,

ACCSEQ,

VEHNUM,

CLASS1,

GADEV1,

OBJCONT,

CLASS2,

GADEV2,

RATWGT

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

///$tab 1994event

LOAD PSU,

[1994CASENO],

RATWGT,

CASEID,

[1994STRATIF],

VERSION,

ACCSEQ,

VEHNUM,

CLASS1,

GADEV1,

OBJCONT,

CLASS2,

GADEV2

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

///$tab 1993GV

LOAD PSU,

AOPSVEH,

CASEID,

CASENO,

STRATIF,

VERSION,

VEHNO,

MODELYR,

MAKE,

MODEL,

BODYTYPE,

VIN,

TOWPAR,

TRAVELSP,

DRINKING,

ALCTEST,

SPLIMIT,

MANEUVER,

ACCTYPE,

DRPRES,

OCUPANTS,

OCCFORMS,

CURBWGT,

CARGOWGT,

TOWHITCH,

DOCTRAJ,

CONDTREE,

ROLLOVER,

FOVERIDE,

ROVERIDE,

ANGTHIS,

ANGOTHER,

DVBASIS,

DVTOTAL,

DVLONG,

DVLAT,

ENERGY,

DVCONFID,

INSPTYPE,

VINLNGTH,

VINJSER,

VINJURED,

DRIVE,

VTREAT,

OTVEHWGT,

OTBDYTYP,

VAIS,

DRUGS,

EVALCLAS,

SPECTEST,

OBSNARC,

SPECNARC,

OBSDEPR,

SPECDEPR,

OBSSTIM,

SPECSTIM,

OBSHLUC,

SPECHLUC,

OBSCNAB,

SPECCNAB,

OBSPCP,

SPECPCP,

OBSINHL,

SPECINHL,

OBSOTH,

SPECOTH,

DRZIP,

DRRACE,

VEHUSE,

ROLINTYP,

ROLINLOC,

ROLLOBJ,

TRIPLOC,

ROLINDIR,

PREMOVE,

PREEVENT,

PRESTAB,

CONSEQ,

RATWGT

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

///$tab 1994GV

LOAD PSU,

CASENO,

RATWGT,

AOPSVEH,

CASEID,

STRATIF,

VERSION,

VEHNO,

MODELYR,

MAKE,

MODEL,

BODYTYPE,

VIN,

TOWPAR,

TRAVELSP,

DRINKING,

ALCTEST,

SPLIMIT,

MANEUVER,

ACCTYPE,

DRPRES,

OCUPANTS,

OCCFORMS,

CURBWGT,

CARGOWGT,

TOWHITCH,

DOCTRAJ,

CONDTREE,

ROLLOVER,

FOVERIDE,

ROVERIDE,

ANGTHIS,

ANGOTHER,

DVBASIS,

DVTOTAL,

DVLONG,

DVLAT,

ENERGY,

DVCONFID,

INSPTYPE,

VINLNGTH,

VINJSER,

VINJURED,

DRIVE,

VTREAT,

OTVEHWGT,

OTBDYTYP,

VAIS,

DRUGS,

EVALCLAS,

SPECTEST,

OBSNARC,

SPECNARC,

OBSDEPR,

SPECDEPR,

OBSSTIM,

SPECSTIM,

OBSHLUC,

SPECHLUC,

OBSCNAB,

SPECCNAB,

OBSPCP,

SPECPCP,

OBSINHL,

SPECINHL,

OBSOTH,

SPECOTH,

DRZIP,

DRRACE,

VEHUSE,

ROLINTYP,

ROLINLOC,

ROLLOBJ,

TRIPLOC,

ROLINDIR,

PREMOVE,

PREEVENT,

PRESTAB,

CONSEQ

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

///$tab 1993OA

LOAD PSU,

CASEID,

CASENO,

STRATIF,

VERSION,

VEHNO,

OCCNO,

AGE,

SEX,

HEIGHT,

WEIGHT,

ROLE,

SEATPOS,

POSTURE,

EJECTION,

EJCTAREA,

EJCTMED,

MEDSTA,

ENTRAP,

MANAVAIL,

MANUSE,

MANPROPR,

MANFAIL,

BAGAVAIL,

BAGDEPLY,

BAGFAIL,

PARUSE,

HEADREST,

SEATTYPE,

SEATPERF,

CHMAKE,

CHTYPE,

CHORIENT,

CHHARNES,

CHSHIELD,

CHTETHER,

INJSEV,

TREATMNT,

MEDFACIL,

HOSPSTAY,

WORKDAYS,

DEATH,

CAUSE1,

CAUSE2,

CAUSE3,

INJNUM,

MAIS,

ISS,

ABELTAVL,

ABELTUSE,

ABELTYPE,

ABLTPROP,

ABLTFAIL,

STORIENT,

GLASGOW,

BLOOD,

BICARB,

RATWGT

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

///$tab 1994OA

LOAD PSU,

CASENO,

RATWGT,

CASEID,

STRATIF,

VERSION,

VEHNO,

OCCNO,

AGE,

SEX,

HEIGHT,

WEIGHT,

ROLE,

SEATPOS,

POSTURE,

EJECTION,

EJCTAREA,

EJCTMED,

MEDSTA,

ENTRAP,

MANAVAIL,

MANUSE,

MANPROPR,

MANFAIL,

BAGAVAIL,

BAGDEPLY,

BAGFAIL,

PARUSE,

HEADREST,

SEATTYPE,

SEATPERF,

CHMAKE,

CHTYPE,

CHORIENT,

CHHARNES,

CHSHIELD,

CHTETHER,

INJSEV,

TREATMNT,

MEDFACIL,

HOSPSTAY,

WORKDAYS,

DEATH,

CAUSE1,

CAUSE2,

CAUSE3,

INJNUM,

MAIS,

ISS,

ABELTAVL,

ABELTUSE,

ABELTYPE,

ABLTPROP,

ABLTFAIL,

BELTSOU,

STORIENT,

GLASGOW,

BLOOD,

BICARB

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Re: Multiple unlinked excel spreadsheets

maxgro — Wed, 26 Mar 2014 21:53:58 GMT

if you want to unlink (20 excel --> 20 qlikview tables, no link between tables) spreadsheets data you can use

qualify *;

in your load script and add a search object with all fields in user interface

Re: Multiple unlinked excel spreadsheets

Colin-Albert — Wed, 26 Mar 2014 22:04:58 GMT

Having the 20 spreadsheets concatenated to a single table in QlikView will give the simplest solution for creating your charts.

If the columns contain the same data, then keep the column names as the are, don't rename the columns to give different names for each spreadsheet.

I would add a field based on filebasename() to add an extra column to identify the year that the data relates to. You may need to slice the filename data to extract the year.

topic Re: Multiple unlinked excel spreadsheets in QlikView

Multiple unlinked excel spreadsheets

Re: Multiple unlinked excel spreadsheets

Re: Multiple unlinked excel spreadsheets

Re: Multiple unlinked excel spreadsheets

Re: Multiple unlinked excel spreadsheets