Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
rmdnkevin
Contributor III
Contributor III

Create QVD File from Multiple Files

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 :

Screenshot_3.png

And this is what it should be :

2.png

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

Thank you.

- Kevin

1 Solution

Accepted Solutions
qlikviewwizard
Master II
Master II

Hi rmdnkevin

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

Capture.PNG

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;

View solution in original post

12 Replies
agarwalrits13
Contributor III
Contributor III

HI Julian,

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

qlikviewwizard
Master II
Master II

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
Contributor III
Contributor III
Author

Here I attach the dummy files for the source

qlikviewwizard
Master II
Master II

Hi rmdnkevin

What is the measure or expression?

rmdnkevin
Contributor III
Contributor III
Author

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

qlikviewwizard
Master II
Master II

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;

1.PNG

Capture.PNG

qlikviewwizard
Master II
Master II

Capture1.PNG

Capture.PNG

rmdnkevin
Contributor III
Contributor III
Author

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
Master II
Master II

Hi rmdnkevin

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

Capture.PNG

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;