Multiple unlinked excel spreadsheets - Qlik Community

Report Inappropriate Content · ‎2014-03-26

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.

swuehl · ‎2014-03-26

I think it should be possible to concatenate your similar data into one fact table.

Could you post your script?

Report Inappropriate Content · ‎2014-03-26

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);

maxgro · ‎2014-03-26

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

Colin-Albert · ‎2014-03-26

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.