topic Re: Several dimension use in QlikView

Several dimension use

pachi12 — Thu, 08 Sep 2022 20:00:46 GMT

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

Re: Several dimension use

Lisa_P — Thu, 08 Sep 2022 23:39:34 GMT

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;

Re: Several dimension use

pachi12 — Fri, 09 Sep 2022 07:52:43 GMT

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

Did I make an error somewhere?

Re: Several dimension use

BrunPierre — Mon, 12 Sep 2022 13:29:46 GMT

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;

Re: Several dimension use

pachi12 — Mon, 12 Sep 2022 07:44:28 GMT

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!

Re: Several dimension use

BrunPierre — Mon, 12 Sep 2022 13:58:59 GMT

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.