Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Thank you Pradosh, this it's working. I will read the two links below..
You cannot pull this directly from the email. You can insert an inline table in qlikview or import through excel.
Thanks.
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
Is the data you showed as sample in separate columns, or one column per row?
-Rob
The data is in one column per row
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
Thank you Pradosh, this it's working. I will read the two links below..