Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Assemble a Date Picking Up Parts of a Text

Hello! Can someone give me a hint?

In the TXT file that I receive, there is text in the third line. Example: "Prize - 02/2019" and need to assemble three date fields with this information.

Screenshot_1.png

1 - Initial date> Expected result 05/01/2019
2 - Final date> Expected result 04/02/2019
3 - Payment date> Expected result 05/02/2019

The payment date was set as follows:

Preload:
LOAD Distinct
// Payment Date
'05'&'/'&SubField(@1,'-',-1) as FAT_Vencimento, FROM [lib://FTP_CF_SEGUROS/DM_*.txt] (txt, codepage is 28591, no labels, delimiter is ',', msq, header is 1 lines) Where WildMatch(@1,'Prêmio*') ;

I do not know if this is correct but for date of payment, but it works. Already the other dates that I need I could not mount. It has to be dynamic, because every month the payment date will change.

When the file is Jan / 2019 the dates have to be these:
1 - Initial date> Expected result 05/12/2018
2 - Final date> Expected result 04/01/2019
3 - Payment date> Expected result 05/01/2019

When the file is fev / 2019 the dates have to be these:
1 - Initial date> Expected result 05/01/2019
2 - Final date> Expected result 04/02/2019
3 - Payment date> Expected result 05/02/2019

When the file is Mar / 2019 the dates have to be these:
1 - Initial date> Expected result 05/02/2019
2 - Final date> Expected result 03/03/2019
3 - Payment date> Expected result 05/03/2019

Screenshot_2.png

Thank you very much for your attention and help.

Hugs.

 

Labels (3)
1 Solution

Accepted Solutions
Highlighted

Re: Assemble a Date Picking Up Parts of a Text

I think you needed to use ' - ' instead of '-' in your subfield function to make sure that the date comes out at 05/02/2019 instead of 05 /02/2019 (with a gap)

Preload:
LOAD Distinct
	 FileBaseName() as PK_FileBaseName,
     Date#('05/' &SubField(@1,' - ',-1),'DD/MM/YYYY') as FAT_Vencimento,
   	 //Date(AddMonths(Date#('05'&'/'&SubField('Prêmio - 01/2019',' - ',-1), 'DD/MM/YYYY'), -1), 'DD/MM/YYYY') as FAT_Initial_Date,
     Date(AddMonths(Date#('05'&'/'&SubField(@1,' - ',-1), 'DD/MM/YYYY'), -1), 'DD/MM/YYYY') as FAT_Initial_Date,
     Date(Date#('05'&'/'&SubField(@1, ' - ',-1), 'DD/MM/YYYY')-1, 'DD/MM/YYYY') as FAT_Final_Date
FROM [DM_*.txt]
(txt, codepage is 28591, no labels, delimiter is ',', msq, header is 1 lines)
Where WildMatch(@1,'Prêmio*')
;

Tested it in QlikView, but should work the same way in Qlik Sense

View solution in original post

4 Replies
Highlighted

Re: Assemble a Date Picking Up Parts of a Text

Try this

Preload:
LOAD Distinct
   '05'&'/'&SubField(@1,'-',-1) as FAT_Vencimento,
   Date(Date#('05'&'/'&SubField('Prêmio - 02/2019',' - ',-1), 'DD/MM/YYYY')-1, 'DD/MM/YYYY') as FAT_Final_Date,
   Date(AddMonths(Date#('05'&'/'&SubField('Prêmio - 01/2019',' - ',-1), 'DD/MM/YYYY'), -1), 'DD/MM/YYYY') as FAT_Initial_Date
FROM [lib://FTP_CF_SEGUROS/DM_*.txt]
(txt, codepage is 28591, no labels, delimiter is ',', msq, header is 1 lines)
Where WildMatch(@1,'Prêmio*')
;

 

Highlighted
Contributor III
Contributor III

Re: Assemble a Date Picking Up Parts of a Text

Hello,

I tested the tip and I need to improve the result.
As it stands, the date is fixed in the code, but it must be dynamic, according to what it finds in the file.

Example:

In the file that come 'Premium - 02/2019', I need to mount the payment date 05/02/2019, start date 05/01/2019 and end date 04/02/2019. In what comes 'Prize - 03/2019', I will need to set the payment date 05/03/2019, start date 05/02/2019 and end date 04/03/2019.

As I used the script:

'05' & '/' & SubField (@ 1, '-', - 1)  as 

and managed to mount the payment date (I imagine), I tried to replace the excerpt from your example with this script, it does not understand whether it is a date, not a result.

Date(AddMonths(Date#('05'&'/'&SubField(@1,'-',-1), 'DD/MM/YYYY'), -1), 'DD/MM/YYYY') as FAT_Initial_Date,
    Date(Date#('05'&'/'&SubField(@1,'-',-1), 'DD/MM/YYYY')-1, 'DD/MM/YYYY') as FAT_Final_Date

I put an attachment to see if I can show better

Att

 

 

 

Highlighted

Re: Assemble a Date Picking Up Parts of a Text

I think you needed to use ' - ' instead of '-' in your subfield function to make sure that the date comes out at 05/02/2019 instead of 05 /02/2019 (with a gap)

Preload:
LOAD Distinct
	 FileBaseName() as PK_FileBaseName,
     Date#('05/' &SubField(@1,' - ',-1),'DD/MM/YYYY') as FAT_Vencimento,
   	 //Date(AddMonths(Date#('05'&'/'&SubField('Prêmio - 01/2019',' - ',-1), 'DD/MM/YYYY'), -1), 'DD/MM/YYYY') as FAT_Initial_Date,
     Date(AddMonths(Date#('05'&'/'&SubField(@1,' - ',-1), 'DD/MM/YYYY'), -1), 'DD/MM/YYYY') as FAT_Initial_Date,
     Date(Date#('05'&'/'&SubField(@1, ' - ',-1), 'DD/MM/YYYY')-1, 'DD/MM/YYYY') as FAT_Final_Date
FROM [DM_*.txt]
(txt, codepage is 28591, no labels, delimiter is ',', msq, header is 1 lines)
Where WildMatch(@1,'Prêmio*')
;

Tested it in QlikView, but should work the same way in Qlik Sense

View solution in original post

Highlighted
Contributor III
Contributor III

Re: Assemble a Date Picking Up Parts of a Text

@sunny_talwar 

Worked perfectly. Thank you!

🙂