topic Re: Transform Data in QlikView

Transform Data

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

Hi ,

This is follow up question which I raised earlier on. If there's a way to pull directly from the email then its fine, but otherwise I can copy and paste in Excel and then import it to Qlikview.

Account number: 122225

Date: 09/01/2018

Method of transaction: XXX Credit

Amount : 233610,97

Account number: 554555

Date: 02/11/2017

Method of transaction: XXX Credit

Amount: 155403,09

Account number: 678919

Date: 16/10/2017

Method of transaction: XXX Credit

Amount: 151698,36

Expected output on my Qlikview application is

Account number	Date	Method of transaction	Amount
122225	09/01/2018	XXX Credit	33610,97
554555	02/11/2017	XXX Credit	155403,09
678919	16/10/2017	XXX Credit	151698,36

Your assistance will be highly appreciated

Re: Transform Data

isingh30 — Mon, 05 Feb 2018 20:23:17 GMT

You cannot pull this directly from the email. You can insert an inline table in qlikview or import through excel.

Thanks.

Re: Transform Data

Siva_Sankar — Tue, 06 Feb 2018 01:12:33 GMT

Hi Bafana,

It is not possible to load directly from the email as the data may not be readable by Qlikview. If it is coming from attachment in the email, then you may refer Loading attachments from multiple emails via QVSource

- Siva

Re: Transform Data

rwunderlich — Tue, 06 Feb 2018 03:37:27 GMT

Is the data you showed as sample in separate columns, or one column per row?

-Rob

Re: Transform Data

zwyne — Tue, 06 Feb 2018 06:33:10 GMT

The data is in one column per row

Re: Transform Data

pradosh_thakur — Tue, 06 Feb 2018 07:13:48 GMT

try this

Table:

GENERIC LOAD 1 AS DUMMY,

PICK(MATCH(1,SUBSTRINGCOUNT(FIELD1,'Account number'),SUBSTRINGCOUNT(FIELD1,'Date'),SUBSTRINGCOUNT(FIELD1,'Method of transaction'),SUBSTRINGCOUNT(FIELD1,'Amount')),

LEFT(FIELD1,14),LEFT(FIELD1,4),LEFT(FIELD1,21),LEFT(FIELD1,6)) AS Column1,

PICK(MATCH(1,SUBSTRINGCOUNT(FIELD1,'Account number'),SUBSTRINGCOUNT(FIELD1,'Date'),SUBSTRINGCOUNT(FIELD1,'Method of transaction'),SUBSTRINGCOUNT(FIELD1,'Amount')),

Mid(FIELD1,16),MID(FIELD1,6),MID(FIELD1,23),MID(FIELD1,9)) AS Column2,

;

LOAD * INLINE [

FIELD1

Account number: 122225

Date: 09/01/2018

Method of transaction: XXX Credit

"Amount : 233610,97"

Account number: 554555

Date: 02/11/2017

Method of transaction: XXX Credit

"Amount: 155403,09"

Account number: 678919

Date: 16/10/2017

Method of transaction: XXX Credit

"Amount: 151698,36"

];

Set vjoin = ;

FOR i = NoOfTables()-1 to 0 STEP -1

LET vTable=TableName($(i));

IF WildMatch('$(vTable)', 'Table.*') THEN

abc:

$(vjoin)

LOAD *,rowno() as r1 RESIDENT [$(vTable)];

Set vjoin = left join ;

DROP TABLE [$(vTable)];

ENDIF

NEXT i

drop Fields DUMMY,r1;

Use full read

The Magic of Dollar Expansions

Use cases for Generic Load | Qlikview Cookbook

Re: Transform Data

zwyne — Tue, 06 Feb 2018 07:35:44 GMT

Thank you Pradosh, this it's working. I will read the two links below..