Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
Highlighted
Contributor III
Contributor III

HI Julian,

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

Highlighted
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?

Highlighted
Contributor III
Contributor III

Here I attach the dummy files for the source

Highlighted
Master II
Master II

Hi rmdnkevin

What is the measure or expression?

Highlighted
Contributor III
Contributor III

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

Highlighted
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

Highlighted
Master II
Master II

Capture1.PNG

Capture.PNG

Highlighted
Contributor III
Contributor III

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

Highlighted
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