Several dimension use

[pachi12]

Hi all,

I would need your help on the below problem.

I know how to group 1 dimension, however when I have several dimensions to group it is not working.

For the lines highligted in red, I need to keep only the yellow one (the line with the latest value for the same Document ID+CountryID+Field). The line to keep can contain "false" or "true" as Answer (even if we see only examples with false in the screenshot below).

I have attached a file with my data if it can help.

My script is:

Data:

LOAD

    "Document ID",

    "Country ID",

    "Field",

    "Date stamp",

    "Answer"

NoConcatenate

LOAD

"Document ID",

"Country ID",

"Field",

"Answer"

LastValue(Date stamp) as Date_stamp

Resident Data

Group By "Document ID", "Country ID", Field, "Answer",

Order By "Document ID", Field;

DROP Table Data;

Would you please help me?

Thanks in advance 

[Lisa_P]

After your original table load, try this:

NewData:
Load [Document ID],
[Country ID],
[Field],
Timestamp(Max([Date stamp])) as MaxDateStamp
Resident Data
Group by [Document ID], [Country ID], [Field];

Left Join(NewData)
Load [Document ID],
[Country ID],
[Field],
[Date stamp] as MaxDateStamp,
[Answer]
Resident Data;

Drop Table Data;

[pachi12]

Thanks Lisa for the proposal but I have an error message that Table 'Data' is not found

Did I make an error somewhere?

[BrunPierre]

Maybe something like this for an alternative approach

Temp:
LOAD [Document ID], 
     [Country ID], 
     Field, 
Timestamp(Timestamp#([Date stamp],'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') AS [Date stamp],
     Answer,
     [Document ID]&'|'&[Country ID]&'|'&Field as %Key
FROM
[..\Downloads\Example data for QlikSense.xlsx]
(ooxml, embedded labels, table is Sheet);

NoConcatenate
Final:
LOAD %Key, 
MAX([Date stamp]) AS [Date stamp]

Resident Temp
Group By %Key
Order By %Key;

Left Join(Final)
LOAD %Key,
     [Document ID], 
     [Country ID], 
     Field,
    [Date stamp],
     Answer
     
Resident Temp;       
DROP Table Temp;     
DROP Field %Key;

[pachi12]

Thanks for the proposal.

I had an error (duplicate fields, and FT table not existing) so I modified the script as follow (I hope this is good):

The max date has been put in each line, however there are still duplicate and "old" value is still visible (i.e. for the country ID "0" only the 'false' value should have been kept):

Is there something worng in the script I updated?

Thanks in advance!

[BrunPierre]

That's the exact output when it's properly sorted. Try again with the modified script and use the formatted Date Stamp as it appears to be an issue in your sample.