Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am parsing a data from a text file and reading two line as a single record for QlikView. I wanted to find out the line break in text file because the length of the last field in the first line of the record varies.
Can any one please help me or I have to use if condition for this?
$(CurrentFileName):
LOAD
// Line 1 of the text file
@1:2T as [PH]
,@3:21T as [CD_NO]
,@6:19T as [MID_14]
,@20:20T as [Replacement]
,@22:24T as [PH_FILLER1]
,@25:54T as [FIRST_NAME]
,@55:84T as [MIDDL_NAME]
,@85:114T as [LAST_NAME]
,@115:133T as [NATIONALITY]
...............
// Line 2 of the text file
@587:588T as [CH],
@589:607T as [CH_CD_NO],
@608:633T as [EMBOSSING_NAME],
@634:642T as [C_LIMIT],
@643:645T as [CURRENCY],
@646:649T as [AUTH_STS],
@650:652T as [D_DAYS],
@653:653T as [FILLER_CH1],
@654:657T as [VALID_FRMDAT],
FROM [$(Path)$(XHB.txt)] (ansi, fix, no labels, header is line, record is 2 lines);
Thanks Yves, I think no one couldnt understand my requirement. But i got a clue from Yves code. Below is the required code which i was expecting.
<pre>
$(CurrentFileName):
LOAD // Line 1 of the text file
@1:2T as [PH]
,@3:21T as [CD_NO]
,@6:19T as [MID_14]
,@20:20T as [Replacement]
,@22:24T as [PH_FILLER1]
,@25:54T as [FIRST_NAME]
,@55:84T as [MIDDL_NAME]
,@85:114T as [LAST_NAME]
,@115:133T as [NATIONALITY]
............... // Line 2 of the text filemid(@1:1330T,index(@1:1330T,'CH000'),2) as [CH],// X(02)
mid(@1:1330T,index(@1:1330T,'CH000')+2,19) as [CH_CD_NO],// X(19) CARD NUMBER
mid(@1:1330T,index(@1:1330T,'CH000')+21,26) as [EMBOSSING_NAME],// X(26) EMBOSSING NAME
mid(@1:1330T,index(@1:1330T,'CH000')+47,9) as [C_LIMIT],// 9(09) CARD LEVEL LIMIT
mid(@1:1330T,index(@1:1330T,'CH000')+56,3) as [CURRENCY],// X(03) BILLING CURRENCY
mid(@1:1330T,index(@1:1330T,'CH000')+59,4) as [AUTH_STS],// X(04) AUTORIZATION STATUS
mid(@1:1330T,index(@1:1330T,'CH000')+63,3) as [D_DAYS],// 9(03) DAYS DELINQUENCY
mid(@1:1330T,index(@1:1330T,'CH000')+66,1) as [FILLER_CH1],// X(01)
mid(@1:1330T,index(@1:1330T,'CH000')+67,4) as [VALID_FRMDAT] // 9(04) EXPIRY DATE - VALID FROM
FROM [$(Path)$(XHB.txt)] (ansi, fix, no labels, header is line, record is 2 lines);
There is not fixed / delimation for the line break but i can identify the postion of break through the value of field CH which always contains the Value CH000 and there is no anyother field present which contain the same value so i can identify the position through this field but i dont know how to do this qlikview. Below is the code which i was trying to identify the position next line. Please help
@&(index(@1:1330T,'CH000')):(index(@1:1330T,'CH000')+2)T as [CH], // X(02)
@&(index(@1:1330T,'CH000')+2):(index(@1:1330T,'CH000')+21)T as [CH_CD_NO],
If you just need to separate in two rows, this will work fine :
data:
load
subfield(raw_text,'CH000',1) as row1,
subfield(raw_text,'CH000',1) as row2
;
LOAD * INLINE [
raw_text
this_is_variable_textCH000this_is_secondline
];
Thanks Yves, I think no one couldnt understand my requirement. But i got a clue from Yves code. Below is the required code which i was expecting.
<pre>
$(CurrentFileName):
LOAD // Line 1 of the text file
@1:2T as [PH]
,@3:21T as [CD_NO]
,@6:19T as [MID_14]
,@20:20T as [Replacement]
,@22:24T as [PH_FILLER1]
,@25:54T as [FIRST_NAME]
,@55:84T as [MIDDL_NAME]
,@85:114T as [LAST_NAME]
,@115:133T as [NATIONALITY]
............... // Line 2 of the text filemid(@1:1330T,index(@1:1330T,'CH000'),2) as [CH],// X(02)
mid(@1:1330T,index(@1:1330T,'CH000')+2,19) as [CH_CD_NO],// X(19) CARD NUMBER
mid(@1:1330T,index(@1:1330T,'CH000')+21,26) as [EMBOSSING_NAME],// X(26) EMBOSSING NAME
mid(@1:1330T,index(@1:1330T,'CH000')+47,9) as [C_LIMIT],// 9(09) CARD LEVEL LIMIT
mid(@1:1330T,index(@1:1330T,'CH000')+56,3) as [CURRENCY],// X(03) BILLING CURRENCY
mid(@1:1330T,index(@1:1330T,'CH000')+59,4) as [AUTH_STS],// X(04) AUTORIZATION STATUS
mid(@1:1330T,index(@1:1330T,'CH000')+63,3) as [D_DAYS],// 9(03) DAYS DELINQUENCY
mid(@1:1330T,index(@1:1330T,'CH000')+66,1) as [FILLER_CH1],// X(01)
mid(@1:1330T,index(@1:1330T,'CH000')+67,4) as [VALID_FRMDAT] // 9(04) EXPIRY DATE - VALID FROM
FROM [$(Path)$(XHB.txt)] (ansi, fix, no labels, header is line, record is 2 lines);