Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day!
in load script i load two fileds from xlsx file:
Distributors:
LOAD
Distributor,
Name
FROM
$(DataPath)Test.xlsx
(ooxml, embedded labels, table is Лист1);
I need to create flag in script, which will write 1 if Distr have more than 1 Name
Neccesarry result in attached picture
Thank you!
Left Join (Distributors)
LOAD Distributor,
Sign(Count(Distinct Name)) als MultNameFlag
Resident Distributors
Group By Distributor;
Hope this helps
regards
Marco
Distributors:
LOAD
Distributor,
If( Exists(Name) , 1 , 0 ) AS Flag,
Name
FROM
$(DataPath)Test.xlsx
(ooxml, embedded labels, table is Лист1);
But this will only flag the second, third or later occurence ... not the first.
So you should do a resident load of the distributors to get a list of those with more than one name.
Or you will have to reprocess the entire Distributors table to update all rows for distributors with more than one name.
Thanks.
Could you please show how to do resident load in my example?
Try with this, it will flag both
Data:
LOAD Distributor&Name as Key,
Distributor,
Name
FROM
C:\Users\Piet.Hein.Stigchel\Downloads\test.xlsx
(ooxml, embedded labels, table is Лист1);
Join load Key,1 as Flag Where Cnt>1;
Load Distributor&Name as Key,
Count(Name) as Cnt
Resident Data Group by Distributor&Name;
Left Join (Distributors)
LOAD Distributor,
Sign(Count(Distinct Name)) als MultNameFlag
Resident Distributors
Group By Distributor;
Hope this helps
regards
Marco
Actually to make it clearer (I started differently) see below code. To explain it uses a so called preceding load on top of a resident load of the original data. Resident loads tables previously loaded, the preceding load is like a piped load that uses the result from the inner load
Data:
LOAD Distributor&Name as Key,
Distributor,
Name
FROM
C:\Users\Piet.Hein.Stigchel\Downloads\test.xlsx
(ooxml, embedded labels, table is Лист1);
Join load Key,1 as Flag Where Cnt>1;
Load Key,
Count(Name) as Cnt
Resident Data Group by Key;
I think that will flag Distr2 and Distr3 as well Marco, since the count for that is 1.
Hi,
good point, thanks.
Maybe this solution works better:
Distributors:
LOAD Distributor, Name
FROM [https://community.qlik.com/servlet/JiveServlet/download/766458-162231/test.xlsx] (ooxml, embedded labels, table is Лист1);
Left Join (Distributors)
LOAD Distributor,
Sign(Count(Name)-1) as MultNameFlag
Resident Distributors
Group By Distributor;
hope this helps
regards
Marco
I'm guessing that you andreyfcdk91 will use Marco's answer as a count of flag where it has to be >1 for multiple names. That's fine, but others looking for answers to a similar problem might be puzzled as to why that answer is correct, as it does not meet your original requirements.
Furthermore, the following script would do the same in a simpler way. As it is a resident load, I think the the sign of Count will always be 1, unless Distributor is Null.
Distributors:
LOAD
Distributor,
Name,
if(Len(Trim(Distributor))>0,1) as Flag
FROM
C:\Users\Piet.Hein.Stigchel\Downloads\test.xlsx
(ooxml, embedded labels, table is Лист1);