Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Create flag in load script

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!

1 Solution

Accepted Solutions
MarcoWedel

‌Left Join (Distributors)

LOAD Distributor,

           Sign(Count(Distinct Name)) als MultNameFlag

Resident Distributors

Group By Distributor;

Hope this helps

regards

Marco

View solution in original post

8 Replies
petter
Partner - Champion III
Partner - Champion III

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.

Anonymous
Not applicable
Author

Thanks.

Could you please show how to do resident load in my example?

stigchel
Partner - Master
Partner - Master

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;

MarcoWedel

‌Left Join (Distributors)

LOAD Distributor,

           Sign(Count(Distinct Name)) als MultNameFlag

Resident Distributors

Group By Distributor;

Hope this helps

regards

Marco

stigchel
Partner - Master
Partner - Master

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;

stigchel
Partner - Master
Partner - Master

I think that will flag Distr2 and Distr3 as well Marco, since the count for that is 1.

MarcoWedel

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;

QlikCommunity_Thread_162541_Pic1.JPG

hope this helps

regards

Marco

stigchel
Partner - Master
Partner - Master

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);