Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
rmdnkevin
New 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
Arjunarao
Honored Contributor II

Re: Create QVD File from Multiple Files

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;

12 Replies
agarwalrits13
New Contributor III

Re: Create QVD File from Multiple Files

HI Julian,

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

Arjunarao
Honored Contributor II

Re: Create QVD File from Multiple Files

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

Re: Create QVD File from Multiple Files

Here I attach the dummy files for the source

Arjunarao
Honored Contributor II

Re: Create QVD File from Multiple Files

Hi rmdnkevin

What is the measure or expression?

rmdnkevin
New Contributor III

Re: Create QVD File from Multiple Files

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

Arjunarao
Honored Contributor II

Re: Create QVD File from Multiple Files

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

Arjunarao
Honored Contributor II

Re: Create QVD File from Multiple Files

Capture1.PNG

Capture.PNG

rmdnkevin
New Contributor III

Re: Create QVD File from Multiple Files

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

Arjunarao
Honored Contributor II

Re: Create QVD File from Multiple Files

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;