ApplyMap()

[Supertramp0]

Hello,

Please find attached my sample Qlikview doc.

The Asssignments table is my main table. It has 2 rows with 2 dats.

My datebridge table has 4 rows. I want to get the two rows from the Assignment table and eliminate duplicates.

The count of file number should only be 2 in the datebridge table - 1 for each date in the Assignment table and not 4

Please advise as to how.

Thanks

Shashank

[baliyan_vinay]

Does distinct not work here? Like this.

Load distinct
filenumber,
Subfield(Applymap('filenumber2AssignedDate',filenumber,Null()),'|') as CanonicalDate,
'Assign' as DateType
Resident [Assignments];

[Supertramp0]

The issue with using Distinct is if there are 2 records for 1 date then the count of filenumbers for that date should be 2.

Distinct will give me 1.

So what I need is:

Date_Assigned  Filenumber

4/13/2020            123

4/13/2020            123

4/14/2020            123

Need 2 for 4/13/2020   and 1 for 4/14/2020  

[Supertramp0]

The issue with using Distinct is if there are 2 records for 1 date then the count of filenumbers for that would be 1

I want a count of 2.

So what I need is:

Date_Assigned  Filenumber

4/13/2020            123

4/13/2020            123

4/14/2020            123

Need 2 for 4/13/2020   and 1 for 4/14/2020  

[alex00321]

Hi not sure why you firstly concat the date then subfield it, this will make the record increase and what is exactly you expected? Thanks!

[Supertramp0]

Hi,

This is what I need from the datebridge table after loading the mapping table.

Date_Assigned  Filenumber

4/13/2020            123

4/13/2020            123

4/14/2020            123

The Date_Assigned field is a datetime field and not a date field. This is why I need all 3 rows from the mapping table and not just the 1st and 3rd.

Thanks

Shashank