topic Re: ApplyMap() in QlikView

ApplyMap()

Supertramp0 — Fri, 10 Apr 2020 19:06:50 GMT

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

Re: ApplyMap()

baliyan_vinay — Mon, 13 Apr 2020 03:04:07 GMT

Does distinct not work here? Like this.

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

Re: ApplyMap()

Supertramp0 — Mon, 13 Apr 2020 21:25:13 GMT

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/14/2020 123

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

Re: ApplyMap()

Supertramp0 — Mon, 13 Apr 2020 21:30:06 GMT

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/14/2020 123

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

Re: ApplyMap()

alex00321 — Tue, 14 Apr 2020 03:24:58 GMT

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!

Re: ApplyMap()

Supertramp0 — Tue, 14 Apr 2020 13:30:58 GMT

Hi,

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

Date_Assigned Filenumber

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