Assemble a Date Picking Up Parts of a Text

[edugallowk]

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.

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

Thank you very much for your attention and help.

Hugs.

[sunny_talwar]

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*')
;

[edugallowk]

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

[sunny_talwar · Accepted solution]

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

[edugallowk]

@sunny_talwar 

Worked perfectly. Thank you!

🙂