Create QVD File from Multiple Files

[rmdnkevin]

Hi, everyone.

I have some files named 20180612, 20180704, 20180808 and etc in csv format and also reportedTicket in xlsx format.

I need to create QVD from those files, I'm trying to create QVD from this script :

Create QVD

SOURCE_DATA: LOAD      email,      first_name,      last_name,      position,      status, Date(Date#(SubField( FileName(),'.',1),'YYYYMMDD'),'DD/MM/YYYY') as Date FROM ..\SOURCE\20******.csv (txt, codepage is 1252, embedded labels, delimiter is ',', msq); STORE SOURCE_DATA INTO ..\QVD\SOURCE_DATA.QVD; DROP TABLE SOURCE_DATA; JUMLAH_REPORT: LOAD Tahun,      Bulan,      Tanggal,      [Jumlah Report],      Date(MakeDate(Tahun,Bulan,Tanggal),'DD/MM/YYYY') as Date FROM ..\SOURCE\reportedTicket.xlsx (ooxml, embedded labels); STORE JUMLAH_REPORT INTO ..\QVD\JUMLAH_REPORT.QVD; DROP TABLE JUMLAH_REPORT;

It works, but, when I put in QVW file, it only shows a single chart like this for the SOURCE_DATA.qvd :

And this is what it should be :

Where did I do it wrong ? Does it in QVD or the script in QVW ?

Thank you.

- Kevin

[agarwalrits13]

HI Julian,

Can you upload sample data from QVD and excelsheet so that I can check where is error?

[qlikviewwizard]

Hi rmdnkevin

We don't know what is the data in the two sources.

Can you attach some sample /dummy data of two source files?

[rmdnkevin]

Here I attach the dummy files for the source

[qlikviewwizard]

Hi rmdnkevin

What is the measure or expression?

[rmdnkevin]

Expression for the chart are only count each status from 2018**** files and sum the reported tiket file

[qlikviewwizard]

Hi,

SOURCE_DATA:

LOAD Tahun,

    Bulan,

    Tanggal,

    [Reported Ticket],

    Date(MakeDate(Tahun,Bulan,Tanggal),'DD/MM/YYYY') as Date

FROM

reportedTicket.xlsx

(ooxml, embedded labels, table is ReportedTicket);

JUMLAH_REPORT_Temp:

LOAD Name,

    Position,

    Status,

    FileName() as FileName

FROM

[20180612.xlsx]

(ooxml, embedded labels, table is Sheet1);

Concatenate

LOAD Name,

    Position,

    Status,

    FileName() as FileName

FROM

[20180704.xlsx]

(ooxml, embedded labels, table is Sheet1);

Concatenate

LOAD Name,

    Position,

    Position1 as Status,//Position1 column must be renamed to Status as per the data

    FileName() as FileName

FROM

[20180808.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

JUMLAH_REPOR:

LOAD *,DATE(DATE#(left(FileName,8),'YYYYMMDD')) as Date Resident JUMLAH_REPORT_Temp;

drop table JUMLAH_REPORT_Temp;

[qlikviewwizard]

[rmdnkevin]

By this way, I think the script cannot load automatically if there's a new source data for the next month, can it ?

*correct me if I wrong

[qlikviewwizard · Accepted solution]

Hi rmdnkevin

Change the column name last column Position to "Status" in 20180808.xlsx as shown in picture.

SOURCE_DATA:

LOAD Tahun,

    Bulan,

    Tanggal,

    [Reported Ticket],

    Date(MakeDate(Tahun,Bulan,Tanggal),'DD/MM/YYYY') as Date

FROM

reportedTicket.xlsx

(ooxml, embedded labels, table is ReportedTicket);

JUMLAH_REPORT_Temp:

LOAD *,

    FileName() as FileName

FROM

[2018*.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

JUMLAH_REPORT:

LOAD *,DATE(DATE#(left(FileName,8),'YYYYMMDD')) as Date Resident JUMLAH_REPORT_Temp;

drop table JUMLAH_REPORT_Temp;