topic Re: Notepad file to QV conversion in QlikView

Notepad file to QV conversion

abhaysingh — Tue, 24 Nov 2015 05:57:10 GMT

Hi all,

i have one dummy data sample file which i need to automate into QV,

first raw is name of excel as output, second raws are headers(column) each header is seperated by " | " sign.

e.g

TAGFile_Extraction = outpit file name

Partner (Full Label)|Agreement (Full Label)|Scheme (Full Label)| Headers for excel

[223] Volkswagen India Private Limited|[2500022] Volkswagen India Assistance|[250002201] Volkswagen Assistance|

i want to pull it into qv first and automate as well.

can any one suggest me this how i can do this.. the format will be standard...

Re: Notepad file to QV conversion

raghvendrasingh — Tue, 24 Nov 2015 06:09:58 GMT

Hi,

Please find qvw as attachment.

In QVW you can put you file path.

Please let me know if your problem is resolved.

Regards,

Raghvendra

Re: Notepad file to QV conversion

jagan — Tue, 24 Nov 2015 06:14:23 GMT

HI,

Try this script

LOAD [Partner (Full Label)],

[Agreement (Full Label)],

[Scheme (Full Label)],

[Guarantee (Full Label)],

[Service (Full Label)],

[Cause (Full Label)],

[Case Created By (Operator Full Name)],

[Service Assigned By (Operator Full Name)],

[Assignment (Creation Date)],

[Assignment (Creation Time)],

[Case (Creation Date)],

[Case (Creation Time)],

[Assignment (Reference)],

[Assignment Status (Full Label)],

[Expected intervention (Date)],

[Expected intervention (Time)],

[Real intervention beginning (Date)],

[Real intervention beginning (Time )],

[Real intervention end (Date)],

[Real intervention end (Time )],

[Case Sub Type (Full Label)],

[Case Type (Label)],

[Incident (Date)],

[Incident Location (Address)],

[Afterwards case?],

[Case (reference)],

[Case (Closing Date)],

[Case (Closing Time)],

[Case (Completed Date)],

[Case (Completed Time)],

[Case (Comment)],

[Case Status (Label)],

[Partner Case (Reference)],

[Vehicle Brand (Label)],

[Vehicle (Model)],

[Vehicle (Registration)],

[Vehicle (First Registration Date)],

[Vehicle (Vehicle Color)],

[Vehicle (Vehicle Mileage)],

[Completion Duration],

[Out of coverage?],

[Arrival place description (garage name)],

[Arrival Address],

[Arrival Town (Name)],

[Arrival Department (Name)],

[Policy (Reference)],

[Policy (Start Date)],

[Policy (End Date)],

[Incident Location (Town)],

[Incident Location Department (Name)],

[Service Cost Estimate Payment Amount With Tax (Foreign)],

[Assisted (Full Name)],

[Caller Full (Name)],

[Caller (Phone Number)],

[Company Caller (Name)],

[Provider (Adress)],

[Provider (Code)],

[Provider (Name)],

[Subscriber (Full Name)],

[Purchase (Date)],

[Mission Actual Distance Value],

[Service Cost Estimate Payment Amount Without Tax (Foreign)],

[Provider (Comment)],

String1,

String2,

String3,

String4,

String5,

String6,

String7,

String8,

String9,

String10,

String11,

String12,

String13,

String14,

String15,

Numeric1,

Numeric2,

Numeric3,

Numeric4,

Numeric5,

Numeric6,

Numeric7,

Numeric8,

Numeric9,

Numeric10,

Numeric11,

Numeric12,

Numeric13,

Numeric14,

Numeric15,

Date1,

Date2,

Date3,

Date4,

Date5,

Date6,

Date7,

Date8,

Date9,

Date10,

Date11,

Date12,

Date13,

Date14,

Date15,

Bool1,

Bool2,

Bool3,

Bool4,

Bool5,

Bool6,

Bool7,

Bool8,

Bool9,

Bool10,

Bool11,

Bool12,

Bool13,

Bool14,

Bool15,

String16,

String17,

String18,

String19,

String20,

String21,

String22,

String23,

String24,

String25,

String26,

String27,

String28,

String29,

String30

FROM

(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

Regards,

jagan.

Re: Notepad file to QV conversion

abhaysingh — Tue, 24 Nov 2015 06:16:43 GMT

hi, thanks,

just look at partner full label column, it is not segrgating by colon..

Re: Notepad file to QV conversion

abhaysingh — Tue, 24 Nov 2015 06:20:52 GMT

Hi jagan,

thanks for revert,

i am also doing the with same script, but the problem is at [Partner (Full Label)] values.. they are not segregating

Re: Notepad file to QV conversion

raghvendrasingh — Tue, 24 Nov 2015 06:23:33 GMT

Hi Abhay,

Sorry i didn't get your reply.Is that any problem in my solution.

Regards,

Raghvendra

Re: Notepad file to QV conversion

jagan — Tue, 24 Nov 2015 06:28:33 GMT

Hi,

There are some double quotes in your file at the beginning of this rows, remove those quotes and reload again.

Regards,

Jagan.

Re: Notepad file to QV conversion

jonathandienst — Tue, 24 Nov 2015 06:31:11 GMT

Like this, perhaps:

LOAD PurgeChar(SubField([Partner (Full Label)], ']', 1), '[') As PartnerCode

Trim(SubField([Partner (Full Label)], ']', 2)) As PartnerLabel,

PurgeChar(SubField([Agreement (Full Label)], ']', 1), '[') As AgreementCode

Trim(SubField([Agreement (Full Label)], ']', 2)) As AgreementLabel,

...

Re: Notepad file to QV conversion

abhaysingh — Tue, 24 Nov 2015 06:32:35 GMT

Hi raghvendra,

Sorry to make confusion for you,

just the open the Partner (Full Label) column and see the values of it, it is not segregating by " | " sign..

Re: Notepad file to QV conversion

abhaysingh — Tue, 24 Nov 2015 06:34:24 GMT

sorry jagan i can not change anything in sample file.. this is something i have to automate.. can we do it in script or before reloading data?

Re: Notepad file to QV conversion

raghvendrasingh — Tue, 24 Nov 2015 06:36:48 GMT

Hi Abhay,

Then as per suggestion from Jagan, you need to remove double quotes from source file and then reload.

Regards,

Raghvendra

Re: Notepad file to QV conversion

abhaysingh — Tue, 24 Nov 2015 06:39:56 GMT

Hi Jonathan thanks for the revert

LOAD PurgeChar(SubField([Partner (Full Label)], ']', 1), '[') As PartnerCode

Trim(SubField([Partner (Full Label)], ']', 2)) As PartnerLabel,

PurgeChar(SubField([Agreement (Full Label)], ']', 1), '[') As AgreementCode

Trim(SubField([Agreement (Full Label)], ']', 2)) As AgreementLabel,

above script giving me output like

Volkswagen India Private Limited|[250002 in partner label column.. againg not segregating by " | " sign

Re: Notepad file to QV conversion

abhaysingh — Tue, 24 Nov 2015 06:47:06 GMT

I mean can't we do it without Removing double quotes manually,, or any other way to remove double quotes automatically?

Re: Notepad file to QV conversion

jagan — Tue, 24 Nov 2015 06:51:36 GMT

Hi,

Please find attached file for solution.

Regards,

Jagan.

Re: Notepad file to QV conversion

abhaysingh — Tue, 24 Nov 2015 06:56:22 GMT

Hi Jagan,

For my understanding , how you loading data in below format?

Data:

LOAD Replace

FROM

[DummyDataNotepad.txt]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Re: Notepad file to QV conversion

abhaysingh — Tue, 24 Nov 2015 07:08:14 GMT

hi jagan,

again same issue values are not segregating with "| " sign..

Re: Notepad file to QV conversion

abhaysingh — Tue, 24 Nov 2015 07:14:44 GMT

hi Jagan,

by using

load replace ( field,'"','') as field

not working still '"', are showing in data output file..pls suggest

Re: Notepad file to QV conversion

jonathandienst — Tue, 24 Nov 2015 07:18:30 GMT

You need a (delimiter is '|') in your load to separate the fields - Jagan pointed that out. I showed how to split the code from the name, which I assumed was what you were asking.

Re: Notepad file to QV conversion

jagan — Tue, 24 Nov 2015 07:31:26 GMT

Hi,

It is working, please refer attached Qlikview file, if that is not you are expecting then attach the sample output you are looking for.

Regards,

Jagan.

Re: Notepad file to QV conversion

jonathandienst — Tue, 24 Nov 2015 07:41:10 GMT

Use the 'no quotes' qualifier to ignore the quotes and then PurgeChar() to get rid of the quotes in the first and last fields:

LOAD PurgeChar(SubField([Partner (Full Label)], ']', 1), '["') As PartnerCode,

Trim(SubField([Partner (Full Label)], ']', 2)) As PartnerLabel,

PurgeChar(SubField([Agreement (Full Label)], ']', 1), '[') As AgreementCode,

Trim(SubField([Agreement (Full Label)], ']', 2)) As AgreementLabel,

...

String29,

PurgeChar(String30, '"') As String30

FROM

(txt, codepage is 1252, embedded labels, delimiter is '|', no quotes);