Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zwyne
Creator
Creator

Transform Data

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

1 Solution

Accepted Solutions
zwyne
Creator
Creator
Author

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

View solution in original post

6 Replies
isingh30
Specialist
Specialist

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

Thanks.

Siva_Sankar
Master II
Master II

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

-Rob

zwyne
Creator
Creator
Author

The data is in one column per row

pradosh_thakur
Master II
Master II

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

Learning never stops.
zwyne
Creator
Creator
Author

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