Logic to load data

[sravan_v007]

Hi All,

I need help in loading data from .txt file.

Data will be in below format..

Sample data:

6200AKFJKFJ KSFJSKJFKFJFJ

6220FJDS FJDSKFJSFJDSKJFDKSJF

6221     ERIWURIEU REIRUIWERUEWORUW

6222AWEUIDFUIFU UFDSIFU

6229QUYERUREYURYUR IEWRUIEW

6230CXNMVNVVN MVNZMN

6220XMNVXMZN XNVCXMVNXCMVNMXCNV

6221EWURIWERU IERUQUR

6223 LJLKJKJJ ASDFSK

6231EIUWRIUEWRI EIRUEWIRUEIWRUIEWUR

6220 YUOIUHJH KSFHSFHS

6221     SJKDFSKJFSKFJSKFJ FJDSKFJ

6225EYRTWEYRTWY REYRTEWYRTEWY

Note:

1. Every transaction starts with 6220. So, data between first 6220 to second 6220 is a single transaction.

     Ex: 6220FJDS FJDSKFJSFJDSKJFDKSJF@6221     ERIWURIEU REIRUIWERUEWORUW@6222AWEUIDFUIFU UFDSIFU@6229QUYERUREYURYUR IEWRUIEW@6230CXNMVNVVN MVNZMN

2. In every transaction 6220 and 6221 is common.

3. After 6221 there is no consistency in next coming value. But it will be ascending order only(i.e. 6222 or 6228).

4. The transaction will start with 6220 but not sure with what value it will end.

5. Each transaction can be in 'n' number of lines.

6. Every number(i.e. 6220, 6221, 6222 etc) has it's own bites to split the data.

Output:  

Field1	Field2	Field3	Field4	Field5	Field6	Field7	Field8	Field9	Field10	Field11	Field12	Field13	Field14
6220	FJDSFJDSKFJ	6221	ERIWURIEU	6222	AWEU	6229	QUYERUR	6230	CXNMVN	-	-	-	-
6220	XMNVXM	6221	EWURIWERU	-	-	-	-	-	-	6223	LJLKJKJJ	6231	EIUWRIUEWRI

Please find attached sample file.

Thanks in advance,

Regards,

Sravan.

[MarcoWedel · Accepted solution]

Hi,

you might either load like:

tabTransactions:

LOAD *,

     If(Code=6220 or Peek(TansactID),RangeSum(Peek(TansactID),-(Code=6220))) as TansactID;

LOAD Left([@1:n],4) as Code,

     Trim(Mid([@1:n],5)) as Data

FROM [https://community.qlik.com/servlet/JiveServlet/download/1208054-264516/test%20(2).txt] (fix, codepage is 1252)

Where Len(Trim([@1:n]));

and use a pivot table to present your data, or generate separate fields for each code using a generic load like:

tabTemp:

LOAD *,

     If(Code=6220 or Peek(TansactID),RangeSum(Peek(TansactID),-(Code=6220))) as TansactID;

LOAD Left([@1:n],4) as Code,

     Trim(Mid([@1:n],5)) as Data

FROM [https://community.qlik.com/servlet/JiveServlet/download/1208054-264516/test%20(2).txt] (fix, codepage is 1252)

Where Len(Trim([@1:n]));

tabTransactions:

Generic

LOAD TansactID,

     'Code'&Code,

     Data

Resident tabTemp

Where TansactID;

hope this helps

regards

Marco

[sravan_v007]

Thanks Marco, it solved my issue.

Regards,

Sravan.

[MarcoWedel]

You're welcome.

Please close your thread by marking a correct answer if your question is answered.

Thanks

Regards

Marco