topic Re: Create QVD File from Multiple Files in QlikView

Create QVD File from Multiple Files

rmdnkevin — Wed, 25 Nov 2020 16:16:04 GMT

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;

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

Re: Create QVD File from Multiple Files

agarwalrits13 — Tue, 24 Jul 2018 02:23:15 GMT

HI Julian,

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

Re: Create QVD File from Multiple Files

qlikviewwizard — Tue, 24 Jul 2018 03:33:58 GMT

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?

Re: Create QVD File from Multiple Files

rmdnkevin — Tue, 24 Jul 2018 03:44:24 GMT

Here I attach the dummy files for the source

Re: Create QVD File from Multiple Files

qlikviewwizard — Tue, 24 Jul 2018 04:16:32 GMT

Hi rmdnkevin

What is the measure or expression?

Re: Create QVD File from Multiple Files

rmdnkevin — Tue, 24 Jul 2018 04:22:01 GMT

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

Re: Create QVD File from Multiple Files

qlikviewwizard — Tue, 24 Jul 2018 04:22:34 GMT

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;

Re: Create QVD File from Multiple Files

qlikviewwizard — Tue, 24 Jul 2018 04:25:52 GMT

Re: Create QVD File from Multiple Files

rmdnkevin — Tue, 24 Jul 2018 04:38:25 GMT

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

Re: Create QVD File from Multiple Files

qlikviewwizard — Tue, 24 Jul 2018 04:50:19 GMT

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;

Re: Create QVD File from Multiple Files

rmdnkevin — Tue, 24 Jul 2018 06:48:56 GMT

Great ! It works, thank you so much

Re: Create QVD File from Multiple Files

qlikviewwizard — Tue, 24 Jul 2018 06:51:18 GMT

Glad to help you

Re: Create QVD File from Multiple Files

rmdnkevin — Wed, 25 Jul 2018 03:23:05 GMT

It's already solved, and also the dummy files already attached in the previous comment